Formula Help

  • Thread starter Thread starter John Young
  • Start date Start date
J

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