Cyberdude
12-11-2006, 03:00 PM
I have two defined names in workbook ?ABC?: CWM2 and BegNW06.
The formula defined by CWM2 is:
=Hist!$Z$1
The formula defined by BegNW06 is:
='C:\EXCEL Documents\XYZ.xls'!NetWorth06
where NetWorth06 is a defined name in workbook ?XYZ.xls? for the formula:
=Stks!$N$12
In workbook ?ABC.xls? if I write the formula:
=INDIRECT(?CWM?&?2?)
I will get the same numeric value that is produced if I write the formula:
=CWM2
However, if in workbook ?ABC.xls? I write the formula:
=INDIRECT(?BegNW?&?06?)
I get the error #REF!.
If I write the formula:
=INDIRECT("'C:\EXCEL Documents\XYZ.xls'!NetWorth"&"06")
I also get the error #REF!. If I write the formula:
= 'C:\EXCEL Documents\XYZ.xls'!NetWorth06
It returns the value of NetWorth06, as expected.
The question is why can I use the INDIRECT function to extract the value of CWM2, but I can?t use INDIRECT to extract the value of NetWorth06? Is this due to the definition of NetWorth06 containing a reference to a cell in another workbook? :banghead:
The formula defined by CWM2 is:
=Hist!$Z$1
The formula defined by BegNW06 is:
='C:\EXCEL Documents\XYZ.xls'!NetWorth06
where NetWorth06 is a defined name in workbook ?XYZ.xls? for the formula:
=Stks!$N$12
In workbook ?ABC.xls? if I write the formula:
=INDIRECT(?CWM?&?2?)
I will get the same numeric value that is produced if I write the formula:
=CWM2
However, if in workbook ?ABC.xls? I write the formula:
=INDIRECT(?BegNW?&?06?)
I get the error #REF!.
If I write the formula:
=INDIRECT("'C:\EXCEL Documents\XYZ.xls'!NetWorth"&"06")
I also get the error #REF!. If I write the formula:
= 'C:\EXCEL Documents\XYZ.xls'!NetWorth06
It returns the value of NetWorth06, as expected.
The question is why can I use the INDIRECT function to extract the value of CWM2, but I can?t use INDIRECT to extract the value of NetWorth06? Is this due to the definition of NetWorth06 containing a reference to a cell in another workbook? :banghead: