Use sheet name in reference to another workbook

[bud31047]

I have two workbooks with identical sheet names( 1x1, 1x2 etc.)
What I want to do is to create a reference that looks at the sheet name where the formula is located and have it point to a cell on the sheet with the same name in the external workbook. Make sense?

I have to write a bunch of these and would rather not have to code each one individually.

Any help would be appreciated.

 

[xlbo]

use faq68-2561
to return the sheet name as a variable in your formula

you will need to hard code the workbook name somewhere

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I simply detest typing long, complicated pathe and file names.

I enter a reference and POINT to the workbook/sheet/range I'm interested in.

Then, if need be, I'll COPY the relevant part(s) of the reference and paste somewhere.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[bud31047]

Thanks for the reply. I appreciate the information, but how would I use this to solve my problem. I copied the code that displays the active sheet name to cell A:1.
If cell A:1 contains a value of "1x1" (active sheet name), how would I write the reference that points to workbook C:\otherworkbook.xls, sheet name that = the value in A:1, and return the value in cell B:1 from that sheet? Like...

='C:\otherworkbook.xls]A:1'!$B$1

Thanks again,

 

[SkipVought]

Copy A:1'!$B$1 to a cell (say A1)
Copy 'C:\otherworkbook.xls] to another cell (say A2)
Copy 'C:\SOMEotherworkbook.xls] to another cell (say A3)
[tt]
=INDIRECT(A2&$A$1)
[/tt]
copy down to next cell to get ref to SOMEotherworkbook

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}