Formula Help

[John Young]

I want to be able to extrapolate data from multiple work
books & place in a new work book. The new work book
contains the file name of the other workbooks to open.

eg:
a
abc.xls
cde.xls
fgh.xls

the formula that you would normally use is

='[abc.xls]sheet 1'!$A$B

is there a way to have the formula say

='[a1]sheet 1'!$A$B
so that when you drag the forumla down the cell it gives
you the change in the a1, to a2, a3 etc so that the file
name changes.

John

 

[acw]

Joh

you can use the indirect function. In B2 put in
=INDIRECT("["&A1&"]sheet1!a1"
and copy down

Note that this will only work while the other sheets are open. You can't use this method to link to closed sheets

Ton


----- John Young wrote: ----

I want to be able to extrapolate data from multiple work
books & place in a new work book. The new work book
contains the file name of the other workbooks to open

eg

abc.xl
cde.xl
fgh.xl

the formula that you would normally use i

='[abc.xls]sheet 1'!$A$

is there a way to have the formula sa

='[a1]sheet 1'!$A$
so that when you drag the forumla down the cell it gives
you the change in the a1, to a2, a3 etc so that the file
name changes

Joh