VLOOKUP - indirect reference to other sheets

[Trevor Shuttleworth]

Dear All

I'm looking for some advice and guidance on building a VLOOKUP formula which
refers to other sheets based on information in a cell. Mmmm ... it doesn't
get any clearer with more words, does it ?

A practical example: I have a summary sheet and individual sheets, one per
day of the month. The tab names for the individual sheets are 01-01
(January 01), 01-02 (January 02), 01-03 (January 03) , etc. to 01-31
(January 31). I want to be able to refer to these sheets in VLOOKUP
formulae in the Summary sheet.

So, in row 2 of the Summary sheet I have the dates in cells B2 to AF2,
January 1, 2004 to January 31, 2004.

In row 3 of the Summary sheet, I can build the reference I need to the
sheets using the following formula:

=TEXT(MONTH(B2),"00")&"-"&TEXT(DAY(B2),"00") which gives me 01-01 in cell
B3, 01-02 in C3, etc

Dragging across gives me the references I need ... or, at least, I think I
need.

So far so good. What I now need to do is build a lookup formula using the
data in Cell B3 (and across to AF3)

What I want to do is create a formula, for example:
=VLOOKUP($A5,'01-01'!B:E,2,FALSE) in cell B5 but, instead of using hard
coded reference to '01-01', I want to be able to use/refer to the contents
of cell B3. I'm guessing this might need to use INDIRECT but I cannot for
the life of me work out the syntax.

The data would look something like:

01/01/2004 02/01/2004 03/01/2004 04/01/2004 05/01/2004
01-01 01-02 01-03 01-04


I'd appreciate any suggestions as to the best way to approach this problem.

Thanks

Trevor

 

[Frank Kabel]

Hi Trevor
try
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E),2,FALSE)

HTH
Frank

 

[Norman Harker]

Hi Trevor!

I think that this is what you want:

=VLOOKUP($A5,INDIRECT("'"&B3&"'!B:E"),2,FALSE)

Look very closely at the quotation marks the first is " ' " (without
spaces). The second is " ' ! without spaces.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Trevor Shuttleworth]

Frank

thanks, I thought it should look something like that but I get the message
"The formula you typed contains an error"

Regards

Trevor

 

[Trevor Shuttleworth]

Norman

thanks very much. I'd been trying to put the INDIRECT around the sheet name
rather than the whole of the lookup range.

I doubt I'd ever have got to this combination.

Thanks again

Trevor

 

[Frank Kabel]

Hi tevor,

sorry I forgot the last ". So change it to
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E"),2,FALSE)

Frank

 

[Trevor Shuttleworth]

Frank

that fixed it. Thank you very much for your help.

Regards

Trevor

 

[Trevor Shuttleworth]

this will save me hours of copying, editing and filling down formulae !

 

[Norman Harker]

Hi Trevor!

Thanks for thanks. Don't tell your boss about the time saved.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Trevor!

Thanks for thanks. Don't tell your boss about the time saved.

so just relax and enjoy...
Frank