Linking to files with a formula

[JVS]

In Excel 2003 what is the trick to linking to a closed work sheet using a
formula?
I'd like to put a list of file names in column A then use a formula to ling
to cells from those books.

Straight link works ='C:\My Docs\[File_1.xls]Sheet1'!X1
Formula does not ='C:\My Docs\[&A4&]Sheet1'!X1

Indirect will not work because I have too many books.
Grateful for any help on this one!

Thanks!
xjvs

 

[Ron de Bruin]

Hi JVS

Maybe you build your formulas with a macro
I use getopenfilename here but it is also possible to loop through the column with file names
http://www.rondebruin.nl/summary2.htm

If you need help to change the macro post back

 

[Ragdyer]

Between versions, this procedure varies slightly.
I'm on an XL97 machine today, and this works for me, but see if it'll work
for you.

*Exact* name of WB in Column A.

Say you click in G1, and paste this formula into the formula bar:

="='C:\My Docs\["&A1&".xls]Sheet1'!X1"

(If you really want the data from cell X1)

Don't worry that what you see in the cell, and what you see in the formula
bar *don't* match!

In this form, you can copy G1 down, and you'll automatically get the "A1" to
increment, so that you obtain *all* the WB names from Column A.

While this Column G range is *still* selected from the original formula
copy, right click in the range and choose "Copy".

Right click in B1 and choose "Paste Special",
Click on "Values", then <OK>.

NOW, while the range in Column B is *still* selected,
<Data> <Text To Columns> <Finish>

And you should have the data in Column B from each of your WBs.

 

[RagDyeR]

Checked this out on my XL02 box and it works fine.

That TTC might be the equalizer between versions.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Between versions, this procedure varies slightly.
I'm on an XL97 machine today, and this works for me, but see if it'll work
for you.

*Exact* name of WB in Column A.

Say you click in G1, and paste this formula into the formula bar:

="='C:\My Docs\["&A1&".xls]Sheet1'!X1"

(If you really want the data from cell X1)

Don't worry that what you see in the cell, and what you see in the formula
bar *don't* match!

In this form, you can copy G1 down, and you'll automatically get the "A1" to
increment, so that you obtain *all* the WB names from Column A.

While this Column G range is *still* selected from the original formula
copy, right click in the range and choose "Copy".

Right click in B1 and choose "Paste Special",
Click on "Values", then <OK>.

NOW, while the range in Column B is *still* selected,
<Data> <Text To Columns> <Finish>

And you should have the data in Column B from each of your WBs.