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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,324
Messages
5,571,551
Members
412,403
Latest member
Iggvsbsb
Top