Inputting a text cell reference into a VLOOKUP formula

amiersen

New Member
Joined
Jan 11, 2005
Messages
21
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

amiersen

New Member
Joined
Jan 11, 2005
Messages
21
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

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
=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.
 

Forum statistics

Threads
1,147,619
Messages
5,742,175
Members
423,708
Latest member
vagosh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top