Indirect references with VLOOKUP
 

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

Indirect references with VLOOKUP
 

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

Indirect references with VLOOKUP
 

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



.

Indirect references with VLOOKUP
 

Why do you include the single quotes?
It appears that this will work the same way:
=VLOOKUP(A4,INDIRECT(""&TEXT(E1,"yymmdd")&"!A2:G5 0"),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.

--
Biff
Microsoft Excel MVP


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



.

Indirect references with VLOOKUP
 

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!

"TQuestar" wrote:

Excel 2007

Indirect references with VLOOKUP
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"TQuestar" wrote:

Excel 2007