Using TabName in a formula

[Gl3n]

Hello all,

I'm trying to use TabName in a formula to complete a function that takes the last populated cell in a given column range within the same sheet, regardless of the sheet name.

I have the TabName VBA simply as:
Function TabName()
TabName = ActiveSheet.Name
End Function

Inside my cell, I've tried:

=LOOKUP(2,1/(TabName(Name!$BA:$BA<>"")),(TabName(Name!$BA:$BA)))

and

=LOOKUP(2,1/(TabName!$BA:$BA<>""),TabName!$BA:$BA)

In a separate sheet where the referenced sheet name isn't dynamic I have =LOOKUP(2,1/(Database!$A:$A<>""),Database!$A:$A) which is working fine.

If there's an alternative to referencing the sheet that the formula is written in I'd be happy to learn some alternatives too!

 

[Joe4]

If you are just checking the same sheet, a sheet reference should not be necessary.
If you leave off the sheet reference, it will just default/apply to the activesheet, i.e.

=LOOKUP(2,1/($BA:$BA<>""),$BA:$BA)

 

[Joe4]

Though it seems unnecessary, in order to get your original idea to work, you need to use the INDIRECT function.
You need to use that whenever dynamicalyl building a range via formula rather than typing it in directly.

That formula would look like:

=LOOKUP(2,1/(INDIRECT(tabname() & "!$BA:$BA")<>""),INDIRECT(tabname() & "!$BA:$BA"))

 

[Gl3n]

Doh! I'm overcomplicating it...just for fun of course...

Thanks again for the save and for the alternative solution if I wanted to keep it unnecessarily complicated

 

[Joe4]

You are welcome.

Yes, the key thing to remember is that is you are ever dynamically building a range in an Excel formula, you need to use the "INDIRECT" functio (to let Excel know that you are actually building a range, and not just a string).