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:G 50"),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" wrote:

Try it like this...

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

--
Biff
Microsoft Excel MVP


"TQuestar" wrote in message
...
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



.