Inserting variables within file names

[kestrel]

Not sure if this can be done, but you never know.

I have a spreadsheet which imports data from various other
spreadsheets, using simple formuals like:

=C:\folder\[spreadsheet.xls]Book1!$Y$12

However, from time-to-time, I need to change the names of the files,
and add in new files.

So I tried to a formula with something like:

="C:\folder\["&A5&"spreadsheet.xls]Book1!$Y$12"

With A5 containing the change in file name (my spreadsheets would be
something like applespreadsheet.xls, orangespreadsheet.xls etc.)

This of course does not work.

Does anyone know of a way I could insert a variable as part of the file
name?

Many thanks for your help.

 

[Bob Phillips]

The technique used here would be INDIRECTing, but that doesn't work with
closed workbooks. See http://makeashorterlink.com/?F2993260A for an
alternative solution, it is VBA but it works.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[kestrel]

Hi, thanks.

I have tried Laurent Longre's MOREFUNC.XLL using indirect.ext

Here is my formula
=INDIRECT.EXT("'C:\folder\["&I2&".xls]Book1!$Y$12")

However, this returns a #VALUE!

Is this the correct way to use the formula?

Thanks

 

[Bob Phillips]

No idea I am afraid, I have never used it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)