Formula with changing Sheet name

  • Thread starter Thread starter Jim
  • Start date Start date
J

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?
 
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)
 
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]

Bob said:
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)

Jim said:
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?
Click to expand...
Click to expand...
 
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

Bob said:
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?
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Back
Top