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: