vlookup tab reference

[dmj120]

I have a workbook that has a summary tab, which "looks up" the info on several other 'facility tabs.' I used Lookup() and index() to make it a nice expandable workbook -just have to add tabs- and start using it, which works great for all the cells with numbers/dates. the issue came up with a "comment cell" so I used vlookup().


This is the formula that works great: LOOKUP(9.99999999999999E+307,INDIRECT("'"&$B7&"'!$k$7:$k$39"))

This is how I have the comment cell on the summary tab: VLOOKUP(C6,'tab name'!$B$7:$AB$22,27,0) to be able to copy the formula down, instead of having to hard code the tab name, is the a way to reference the tab name similar to the above lookup()?

Thanks
Josh

 

[Jonmo1]

Try

=VLOOKUP(C6,INDIRECT("'"&$B7&"'!$B$7:$AB$22"),27,0)

 

[dmj120]

Thanks, I tried that before -and again in case I messed it up before- and it when hitting enter, a window pops up for me to click a file. I even tried cntl+****+enter see if adding {} would help....nada.

 

[Jonmo1]

Works for me, provided B7 contains a valid Sheet Name.

 

[dmj120]

Works for me, provided B7 contains a valid Sheet Name.

UGH Thanks! It does work, it was the first half of the formula -if(iserror()..) that was the problem. Thank you very much

 

[Jonmo1]

You're welcome.