Indirect references with VLOOKUP

[TQuestar]

RE: Excel 2007

I have a workbook, with a SUMMARY worksheet (tab), where the first column
(A) contains stock ticker symbole, e.g. XOM. In addition, there are many more
worksheets (tabs), all labelled in this way 091231, 100131, etc That is,
YYMMDD.

In all those pages I have a I have an area A2:G50, where the first column
contains a stock ticker symbol, and columns B, C, etc. have various data.

In the SUMMARY worksheet I want to callup data from the other sheets. For
example, say SUMMARY!A4 is XON

I could have =VLOOKUP(A4,'091231'!A2:G50,2,1)
and that would return the value in sheet 091231 in the row where XOM is in
the first column. So far, so good.

Here is my problem, I want the lookup formula to refer to an input cell in
SUMMARY, say E1, and I want to be able to input the value 12/31/09 into E1,
and have the cell with VLOOKUP function return the value I want from
worksheet 091231. I want something like this, but this fails, and every
variation I can think of fails as well:

=VLOOKUP(A4,cell("contents",E1)!A2:G50,2,1)

I think I have two problems:
1. VLOOKUP requires a as a sheet name, but "12/31/09" is a value

2. Something more is required, as even if I enter "091231" or "'091231" into
E1, this fails.

I will appreciate any help, as I have tried everything I can think of.

TQuestar

 

[T. Valko]

Try it like this...

=VLOOKUP(A4,INDIRECT("'"&TEXT(E1,"yymmdd")&"'!A2:G50"),2,1)

 

[TQuestar]

Bingo! Thanks ever so much for this hint, which does exactly what I wanted. I
did not know the TEXT function, which I can now see is very valuable for a
lot of situations.

One thing does puzzle me, however. Why do you include the single quotes? It
appears that this will work the same way:

=VLOOKUP(A4,INDIRECT(""&TEXT(E1,"yymmdd")&"!A2:G50"),2,1)
=VLOOKUP(A4,INDIRECT("'"&TEXT(E1,"yymmdd")&"'!A2:G50"),2,1)

I include your original after my mod to make my question clear.

I wish Excel color-coded quotes the way it does parenthesis, as that would
perhaps help me understand this question.

In any case, thank you very much!

TQ

 

[T. Valko]

Why do you include the single quotes?

It appears that this will work the same way:
=VLOOKUP(A4,INDIRECT(""&TEXT(E1,"yymmdd")&"!A2:G50"),2,1)

The single quotes is just a habit. If a sheet name contains space characters
or numbers Excel uses the single quotes to delimit the sheet name when
referencing it.

For example, if you have a sheet named 091231 and you enter a simple link
formula like this:

=091231!A1

Excel will add the single quotes so that the formula ends up as:

='091231'!A1

However, in the lookup application we really don't need them in the lookup
formula because Excel will automatically evaluate the sheet name with them.
So, we can write the formula like this:

=VLOOKUP(A4,INDIRECT(TEXT(E1,"yymmdd")&"!A2:G50"),2,1)

However (!), if the sheet name to be referenced was something like Sheet 3,
then we do need the single quotes as Excel will not add them:

E1 = Sheet 3

=VLOOKUP(A4,INDIRECT(E1&"!A2:G50"),2,1)

Returns the #REF! error.

=VLOOKUP(A4,INDIRECT("'"&E1&"'!A2:G50"),2,1)

Should work.

So, including the single quotes won't hurt anything if they're not needed so
it's just a "good" habit to include them although they make the formula a
bit more cryptic to read.

 

[TQuestar]

Again, thanks for the clear explanation.

This is just the sort of small but vital information that I can't find in
books. Everybody talks about the big complicated topics like arrays and macro
programming, but pass over the single quotes!

 

[T. Valko]

You're welcome. Thanks for the feedback!