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
try
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E),2,FALSE)

HTH
Frank


Trevor Shuttleworth wrote:
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

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

Regards

Trevor


"Frank Kabel" wrote in message
...
Hi Trevor
try
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E),2,FALSE)

HTH
Frank


Trevor Shuttleworth wrote:
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 tevor,

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

Frank


Trevor Shuttleworth wrote:
Frank

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

Regards

Trevor


"Frank Kabel" wrote in message
...
Hi Trevor
try
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E),2,FALSE)

HTH
Frank


Trevor Shuttleworth wrote:
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

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

Regards

Trevor


"Frank Kabel" wrote in message
...
Hi tevor,

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

Frank


Trevor Shuttleworth wrote:
Frank

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

Regards

Trevor


"Frank Kabel" wrote in message
...
Hi Trevor
try
=VLOOKUP($A5,INDIRECT("'" & B3 & "'!B:E),2,FALSE)

HTH
Frank


Trevor Shuttleworth wrote:
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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Trevor Shuttleworth" wrote in message
...
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

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


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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Trevor Shuttleworth" wrote in message
...
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

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

Hi Trevor!

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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Norman Harker wrote:
Hi Trevor!

Thanks for thanks. Don't tell your boss about the time saved.
:-)
so just relax and enjoy...
Frank