Formula with changing Sheet name

[Jim]

I would like to create a formula that will lookup values
in another workbook, but will lookup in different sheets
depending on where the formula is located in my own
workbook.

It will look something like this:
=VLOOKUP($A21,'\\networkloaction\[filename.xls]January'!
$A$1:$Z$100,FALSE)

The lookup range on each sheet would be the same, but I
would want the sheet name to change from January to
February or March (etc.), depending on where my formula
was located. Is there some way of using the INDIRECT
function, or something, to turn the sheet name into a
dynamic reference?

 

[Bob Phillips]

Jim,

Haven't tried it but it should be something like

=VLOOKUP($A21,INDIRECT("'\\networkloaction\[filename.xls]'" & A17 &
"'!$A$1:$Z$100"),FALSE)

where A17 woiuld hold January, the sheet name.

You could also use TEXT(A17,"mmmm") if it held a date.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

But if that file is closed (and a good chance it is since it includes the UNC
path), then Indirect won't work.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]

Jim,

Haven't tried it but it should be something like

=VLOOKUP($A21,INDIRECT("'\\networkloaction\[filename.xls]'" & A17 &
"'!$A$1:$Z$100"),FALSE)

where A17 woiuld hold January, the sheet name.

You could also use TEXT(A17,"mmmm") if it held a date.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I would like to create a formula that will lookup values
in another workbook, but will lookup in different sheets
depending on where the formula is located in my own
workbook.

It will look something like this:
=VLOOKUP($A21,'\\networkloaction\[filename.xls]January'!
$A$1:$Z$100,FALSE)

The lookup range on each sheet would be the same, but I
would want the sheet name to change from January to
February or March (etc.), depending on where my formula
was located. Is there some way of using the INDIRECT
function, or something, to turn the sheet name into a
dynamic reference?

 

[Jim]

Thank you both. I will be able to work something out
with your advice. Cheers.

-----Original Message-----
But if that file is closed (and a good chance it is since it includes the UNC
path), then Indirect won't work.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/groups?threadm=oZxYa.14760$cJ5.1412% 40www.newsranger.com

Jim,

Haven't tried it but it should be something like

=VLOOKUP($A21,INDIRECT ("'\\networkloaction\[filename.xls]'" & A17 &
"'!$A$1:$Z$100"),FALSE)

where A17 woiuld hold January, the sheet name.

You could also use TEXT(A17,"mmmm") if it held a date.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I would like to create a formula that will lookup values
in another workbook, but will lookup in different sheets
depending on where the formula is located in my own
workbook.

It will look something like this:
=VLOOKUP($A21,'\\networkloaction\[filename.xls] January'!
$A$1:$Z$100,FALSE)

The lookup range on each sheet would be the same, but I
would want the sheet name to change from January to
February or March (etc.), depending on where my formula
was located. Is there some way of using the INDIRECT
function, or something, to turn the sheet name into a
dynamic reference?

--

Dave Peterson
(e-mail address removed)
.