# Cell Contents as Worksheet Name in Formula

#### TAllen

##### Board Regular
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

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

Replies
0
Views
57
Replies
2
Views
107
Replies
3
Views
59
Replies
3
Views
80
Replies
5
Views
164