Inputting a text cell reference into a VLOOKUP formula

[amiersen]

I have a program that uses tables on various worksheets according to user-input specifications. In cell A41, I use VB to write the name of the worksheet I need to use
ex. 10YrBandC

Instead of hardcoding a vlookup formula in cell A50 to use a specific worksheet, I want it to use the worksheet that is specified in cell A41. Right now my vlookup formula looks like this:

=VLOOKUP($B$15,'10YrBandC'!A1:M100,$D$22)

I want to replace 10YrBandC with whichever worksheet name happens to be in cell A41 at the time.

Thanks!

 

[Todd Bardoni]

Have a look at the INDIRECT function.

 

[amiersen]

I've been trying INDIRECT for a while, but it's still not liking my formula. Do you know exactly how to format it?

 

[Todd Bardoni]

=VLOOKUP($B$15,INDIRECT("'"&A41&"'!A1:M100"),$D$22)

Note:- Indirect will not work with closed workbooks. But, It doesn't appear that you're referring to another workbook so this may not matter in your case.

 

[amiersen]

Thank you VERY much!

 

[steve case]

I found this on a search, & it does exactly what I want!