Cell Contents as Worksheet Name in Formula

TAllen

Board Regular
Joined
Dec 7, 2005
Messages
84
Note the function below:

=VLOOKUP($D6,'WORKSHEET NAME'!$A$7:$AL$30,9,FALSE)

The "Table Array" portion of my formula refers to a range of cells on another worksheet. What I want to do is use the contents of cell A6 on the current worksheet to define the worksheet name to be used in the VLOOKUP function.

A simple cell reference like that shown below doesn't work regardless of whether or not the quotation marks are included:

=VLOOKUP($D6,'A6'!$A$7:$AL$30,9,FALSE)

I have also tried using CELL("contents",A6) but this doesn't work either. Does anyone know if it is possible to do what I want to do and, if so, how? Thanks.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi TAllen

Welcome to the Board!

You can use Indirect:

=VLOOKUP($D6, INDIRECT(A6),9,FALSE)

this assumes that in A6 you have the following:

'Worksheet Name'!$a$7:$a$30

Regards

Richard
 

TAllen

Board Regular
Joined
Dec 7, 2005
Messages
84
Thanks for the reply, Richard. The only thing I have in A6 is a 9 character text string. For example: 018512345. This text string is an identification number (for a site I am working on) and is also the Worksheet Name. In other words, A6 doesn't contain the range definition ($a$7:$a$30).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,148
Members
430,342
Latest member
Sailingexcel

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