Referencing NAMES in Other Workbooks

[DonnieB]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,
I have some basic data that I will be using over and over again in other worksheets. So, I will be using NAMES to make that easy. This is my master data.
I know how to reference the Master data by use of the NAMES in my formulas in the OTHER WORKBOOKS. But what I want to be able to do is in Column B, for example, type in the NAME used in the MASTER DATA workbook, then in Column C, display the value from the MASTER DATA Workbook. No problem if I want manually type in the NAME in each reference formula to the MASTER DATA. But what I want to do is reference the NAME that I have typed in Column B of THIS WORKBOOK and have it insert into the formula in COLUMN C? I am sure there is a simple way to do this... but I can not find it.
Thanks...

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,
I have some basic data that I will be using over and over again in other
worksheets. So, I will be using NAMES to make that easy. This is my master
data.
I know how to reference the Master data by use of the NAMES in my formulas in
the OTHER WORKBOOKS. But what I want to be able to do is in Column B, for
example, type in the NAME used in the MASTER DATA workbook, then in Column C,
display the value from the MASTER DATA Workbook. No problem if I want manually
type in the NAME in each reference formula to the MASTER DATA. But what I want
to do is reference the NAME that I have typed in Column B of THIS WORKBOOK and
have it insert into the formula in COLUMN C? I am sure there is a simple way
to do this... but I can not find it.
Thanks...

This should work fine using the INDIRECT function. Check Excel help for an
explanation and examples. Post back if you are still having problems.

 

[DonnieB]

I am not exactly sure How to use this. Do I embed the INDIRECT function into my formula that references the MASTERDATA workbook? For Examp... ='Masterdata.xlsx'!F.01 Where the F.01 is the NAME. Okay.. so I would embed it such ='Masterdata.xlsx'!INDiRECT(C4) and C4 would have the NAME...
F.01 ?? If this is correct.. I still can not find how to properly format the INDIRECT function into the formula.

Thanks for you help.

 

[Bob Greenblatt]

I am not exactly sure How to use this. Do I embed the INDIRECT function into
my formula that references the MASTERDATA workbook? For Examp...
='Masterdata.xlsx'!F.01 Where the F.01 is the NAME. Okay.. so I would embed it
such ='Masterdata.xlsx'!INDiRECT(C4) and C4 would have the NAME...
F.01 ?? If this is correct.. I still can not find how to properly format the
INDIRECT function into the formula.

Thanks for you help.

Try indirect("'"masterdata.xlsx"'"&"!"&c4)

 

[DonnieB]

Thanks Bob... but I DO NOT UNDERSTAND.
Do I need to put quotes exactly as you have shown?

Like this:
=indirect(""mac hard drive:Users:donnieocuments:STRAUB:HardwareMasterMH.xlsx""&"!"&c4)

This does not work ?? Says there is an error.

 

[Bob Greenblatt]

Thanks Bob... but I DO NOT UNDERSTAND.
Do I need to put quotes exactly as you have shown?

Like this:
=indirect(""mac hard
drive:Users:donnieocuments:STRAUB:HardwareMasterMH.xlsx""&"!"&c4)

This does not work ?? Says there is an error.

Yes, you need the quotes exactly as below, NOT as you stated above.
=indirect("'"driveath:filename'!"&c4)

Of course this assumes that the value of cell C4 contains the text of a name
defined in the workbook at driveath:filename.

 

[DonnieB]

Still does not work. You have typed within the function parenthesis double quotes then an apostrophe then the file path then apostrophe then ! then single quotes then & then cell reference.

And that is exactly what I have done.

=INDIRECT(""'mac hard drive:Users:donnieocuments:STRAUB:HardwareMasterMH.xlsx'!"&C4)

And the cell reference does reference the cell that contains the NAME from the master data work book.

Sorry for being so dense.

 

[Bob Greenblatt]

Still does not work. You have typed within the function parenthesis double
quotes then an apostrophe then the file path then apostrophe then ! then
single quotes then & then cell reference.

And that is exactly what I have done.

=INDIRECT(""'mac hard
drive:Users:donnieocuments:STRAUB:HardwareMasterMH.xlsx'!"&C4)

And the cell reference does reference the cell that contains the NAME from the
master data work book.

Sorry for being so dense.

It is double quote, single quote, driveath:file, single quote,
exclamation, double quote

 

[DonnieB]

Yup.. That works. Thanks.
The quotes and apostrophe thing is always very confusing. Is there some kind of an overall "guideline" that explains the standard "conventions"?

 

[Bob Greenblatt]

Yup.. That works. Thanks.
The quotes and apostrophe thing is always very confusing. Is there some kind
of an overall "guideline" that explains the standard "conventions"?

Yes, the syntax for naming ranges in excel. It is: single apostrophe, fully
qualified path and file name, single apostrophe, left bracket, sheet name,
right bracket, exclamation, range name.