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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> kestrel
> ------------------------------------------------------------------------
> kestrel's Profile:
http://www.excelforum.com/member.php...o&userid=19082
> View this thread: http://www.excelforum.com/showthread...hreadid=552254
>

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

"kestrel" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> kestrel
> ------------------------------------------------------------------------
> kestrel's Profile:
http://www.excelforum.com/member.php...o&userid=19082
> View this thread: http://www.excelforum.com/showthread...hreadid=552254
>