substitute the filename in a cell reference with a string in another cell.

[flummi]

Hi all,

suppose I have this cell reference: ='c:\[file.xls]sheet1'!X1

and I have the file name "file.xls" in cell A1 on my active worksheet

Is there a way to any of the following:

- include the string in A1 in the above cell reference between the
square brackets?
- take the complete path (c:\[file.xls]) from a cell on my worksheet?

or do I have to TYPE the complete path in the above cell reference?

Thanks for thinking about it.

Hans

 

[Guest]

Hi,

Assuming "file.xls" is in A1, type the following in any cell of active sheet.

="'c:\["&A1&"]sheet1'!X1"

and take the result of this cell.

With kind regards,

NAVEEN

 

[vezerid]

Hans

To include another cell's value in the reference you need to use the
INDIRECT function

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

To get the full path you use the CELL function. CELL("filename") will
return the full path, followed by sheet name. To get just the path:

=LEFT(CELL("filename"),FIND("]",CELL("filename")))

HTH
Kostis Vezerides

 

[flummi]

Hi Naveen,

thanks for your reply.

That's how far I had got. But the next bit is the interesting part.

When you say "take the result of this cell" how would you do that?

Let me give you an example. Suppose X1 actually is a range X1:X10

How would you incorporate the string you suggested (lets say in B1)
into e.g. an =average function?

=average(????,X1:X10)

Thanks,

Hans

 

[vezerid]

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

 

[flummi]

Thanks all for the excellent brainwork!

Till next time.

Hans

 

[flummi]

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]

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

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

 

[flummi]

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

 

[vezerid]

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

 

[flummi]

Correct!

When the file is open it displays the correct result.

Thanks Kostis, for taking the time.

Hans

 

[vezerid]

You are welcome Hans. Glad to be of help.

Kostis