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

[NAVEEN]

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

"flummi" wrote:

> 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
>
>

[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

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