VLOOKUP - indirect reference to other sheets

  • Thread starter Thread starter Trevor Shuttleworth
  • Start date Start date
T

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

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

Regards

Trevor
 
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
 
Hi tevor,

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

Frank
 
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.
 
Back
Top