Cell Contents as Worksheet Name in Formula

[TAllen]

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.

 

[Richard Schollar]

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]

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