how do I use a file list to recall data from listed files?
 

I have a huge excel master workbook with 30 worksheets to report data from 50
other workbooks... to add a single workbook to the master file is a huge
task...

I want to create a list of files to call in and automatically add to the
growing list.

Example:

where Filename "test.xls" is in cell A2

='[A2]sheet1!'F13

having said that I want A2 to step up as I drag and expand the list...

how do I use a file list to recall data from listed files?
 

Dave

With a list of workbook names in A2:A10 and "F13" in B2

=INDIRECT("'["&A2&"]Sheet1'!"&$B$2)


Gord Dibben MS Excel MVP


On Wed, 17 Jan 2007 23:58:00 -0800, Dave wrote:

I have a huge excel master workbook with 30 worksheets to report data from 50
other workbooks... to add a single workbook to the master file is a huge
task...

I want to create a list of files to call in and automatically add to the
growing list.

Example:

where Filename "test.xls" is in cell A2

='[A2]sheet1!'F13

having said that I want A2 to step up as I drag and expand the list...

how do I use a file list to recall data from listed files?
 

I've been reading about INDIRECT... the file has to be open with this
function, correct?

I've got a list of 55 files which will grow to over 120... a bit too much to
have open at once....

"Gord Dibben" wrote:

Dave

With a list of workbook names in A2:A10 and "F13" in B2

=INDIRECT("'["&A2&"]Sheet1'!"&$B$2)


Gord Dibben MS Excel MVP


On Wed, 17 Jan 2007 23:58:00 -0800, Dave wrote:

I have a huge excel master workbook with 30 worksheets to report data from 50
other workbooks... to add a single workbook to the master file is a huge
task...

I want to create a list of files to call in and automatically add to the
growing list.

Example:

where Filename "test.xls" is in cell A2

='[A2]sheet1!'F13

having said that I want A2 to step up as I drag and expand the list...

how do I use a file list to recall data from listed files?
 

And can I 'grow' the variable cell number a fill in series?

"Gord Dibben" wrote:

Dave

With a list of workbook names in A2:A10 and "F13" in B2

=INDIRECT("'["&A2&"]Sheet1'!"&$B$2)


Gord Dibben MS Excel MVP


On Wed, 17 Jan 2007 23:58:00 -0800, Dave wrote:

I have a huge excel master workbook with 30 worksheets to report data from 50
other workbooks... to add a single workbook to the master file is a huge
task...

I want to create a list of files to call in and automatically add to the
growing list.

Example:

where Filename "test.xls" is in cell A2

='[A2]sheet1!'F13

having said that I want A2 to step up as I drag and expand the list...