Excel; summarising multiple files

[JamesMichell]

I have a spreadsheet which is summarizing data contained in up to 100 other spreadsheets. Each "external" sheet is named logically by the day and month and there are 2 per day; e.g 2610D.xls and 2610N.xls = 26th Oct Day/Night
What I need is an easy way of referencing the same cell in each of the sheets to bring data into my summary. Writing out the formula each time with the right file name reference is very tedious. Using the fill command does not increment the file name so I can't use that. How can I construct a formula to tell my summary sheet what external sheet to reference?

Any ideas? - gratefully received!

James

 

[SkipVought]

Hi,

You could use the INDIRECT function to pick the workbook and worksheet name from a cell in your worksheet...

=INDIRECT(I3&B2")

where I3 contains the workbook & worksheet name...

[MyWorkbook.xls]MySheet!

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JamesMichell]

Thanks for your quick reply. Am I right in thinking that the external workbook has to be open for Indirect to work? In which case this solution wouldn't work for me - but thanks anyway.
Just to make it clear the part of my present formula which references the cell in the external sheets looks like: +H:\work\shifts\october\[sl2610D.xls). Most of that is constant but the numerals increment so ideally the numerals part of the file name should reference a list which would be easy to increment with the fill function.

James

 

[SkipVought]

External Workbook MUST be open for INDIRECT to work, yes.

Try this. Enter a range of cells with the workbook name san extension.

Then, assuming that the corresping row in column B will hold the formula in question...

for each f in YourWorkbookNameRange
  with f
  cells(.row, "B").Formula = "='H:\work\shifts\october\[" & .value & ".xls]Sheet1'!A1"
  end with
next

Hope this might help



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JamesMichell]

Skip, thanks for this.

As a non programmer I'm going to have to spend some time working it out, but I'm not sure about it...

James

 

[SkipVought]

If you need a specific solution, you need to supply more specific information.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JamesMichell]

Thanks, and I appreciate the time you've already spent on this. I'm going to spend some more time on this later today and I may then post here with more information if I can't get the solution I want with the info you have already given me.

Thanks again

James