I had not realized that INDIRECT is not needed for the workbook part of
a reference (thanks for the post Naveen). But it certainly is needed
for range specifications in functions

=AVERAGE(INDIRECT("'c:\["&A1&"]sheet1'!X1:X10")

HTH
Kostis Vezerides

Thanks all for the excellent brainwork!

Till next time. :-)

Hans

Sorry, it doesn't work. Always delivers a #REF.

A1: Example.xls
=INDIRECT("'C:\["&A1&"]Expenses'!$G$12") gives #REF

This is what ="'C:\["&A1&"]Expenses'!$G$12" delivers:

'C:\[Example.xls]Expenses'!$G$12

Maybe it's not possible at all?

Hans

Hans,
I tested all the solutions I provided.

If your formula delivers
'C:\[Example.xls]Expenses'!$G$12
and you want the INDIRECT of this and you are getting #REF!, is it
possible that:
- Example.xls is not directly in C:\ ?
- Example.xls does not contain a sheet Expenses?

Regards

Kostis

flummi Wrote:
Sorry, it doesn't work. Always delivers a #REF.

A1: Example.xls
=INDIRECT("'C:\["&A1&"]Expenses'!$G$12") gives #REF

This is what ="'C:\["&A1&"]Expenses'!$G$12" delivers:

'C:\[Example.xls]Expenses'!$G$12

Maybe it's not possible at all?

Hans


--
vezerid
------------------------------------------------------------------------
vezerid's Profile: http://www.excelforum.com/member.php...o&userid=28481
View this thread: http://www.excelforum.com/showthread...hreadid=514836

Thanks Kostis,

for investigating this. Hope I don't take too much of your time.

What I did is this:

I loaded the workbook "example.xls
Then I created a new workbook
In this new workbook I created a simple cell reference to a cell in
example.xls
Then I closed example.xls
That gave me the full path in my reference in the new workbook.
Then I copied the filename "example.xls" from the above reference into
cell A1 in my new workbook
Then I copied the formula into another cell and modified it as you
suggested (indirect(....)
That gave me the #REF whilst the old formula still delivered the
correct result.

Here's a "screenshot"

Example.xls

904 #REF!

='C:\[Example.xls]Expenses'!$G$12 <-- formula that delivers 904
=INDIRECT("'c:\["&A1&"]Expenses'!$G$12") <-- formula that delivers
#REF
'c:\[Example.xls]Expenses'!$G$12 <-- this is what I get when I strip
off the indirect() function from the previous command

Hans

Hans,

I think it has to do with the file being closed. If you insert INDIRECT
while the referenced file is open it will display correctly 904. If you
close it nothing will change until recalculation takes place. Try it
and let me know.

Kostis

Correct!

When the file is open it displays the correct result.

Thanks Kostis, for taking the time.

Hans