PDA

View Full Version : Solved: Why Doesn?t This INDIRECT Work?


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:
Shazam
12-11-2006, 05:11 PM
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:


Hi Cyberdude,

I duplicated your problem but it works for me. The only time I get a #REF! error is when the workbook that you are referencing is close. When using the INDIRECT function that referencing another wokbook the file needs to be open for the formula to work.
Simon Lloyd
12-12-2006, 12:27 AM
Cyberdude isn't this post directly related to this one http://www.vbaexpress.com/forum/showthread.php?t=10536 you posted a couple of days earlier?

Regards,
Simon
Cyberdude
12-12-2006, 01:17 PM
Yes, Simon it is. However, this one seemed more generic so I decided to open a new thread. Perhaps that was a bad choice. Sorry.
Sid
Cyberdude
12-12-2006, 01:23 PM
When using the INDIRECT function that referencing another wokbook the file needs to be open for the formula to work. Shazam, you hit the nail on the head. I knew that and just forgot it. That is, of course, the cause of the problem. Duh!
Fortunately, I figured out a workaround that permits me to create the names I want.

I appreciate the reminder. Thanks for your time.
Sid