What is my best strategy here?????

[ajmckenna]

What is my best option for efficiency and to avoid errors in consolidating files?

I need to consolidate large forecast models in to a Master summary for the company.

I have ~20 Separate fcst models all with the same structure. If I want to consolidate one worksheet from each forecast file( they are all located in different directories and are ~130 rows by 130 columns) into a Master Summary P&L Am I better off

1)writing a macro to copy in the 20 individual worksheets as values into their own sheet and then summing them up in the 21st worksheet
or
2)As my MIS guys suggests have one master file linked to all the separate files, formula would be huge though

e.g. =('C:TheProgramsProgram1[Program1P&L.xls]P&LSummary'!C8)+('C:TheProgramsProgram2[Program2P&L.xls]P&LSummary'!C8)+.......+('C:TheProgramsProgram20[Program20P&L.xls]P&LSummary'!C8)

if I use this way is there a way to shorten the length of the formula. i.e to list all the directories to use in a range in the masterfile so that a formula in the Masterfile cell could refer to this list like sum("files in named range"C8)

I am worried that with 20+ files the links could either breakdown or there is not enough space in the cell to hold the length of the formulas. Also wouldn't it be difficult to add the 21, 22, 23 programs as they arise? would have to type them in the first cell and then copy them.

I wanted to use Macros but my MIS guy says links would be more efficient and less prone to error

Help! thanks all

 

[gplhl]

if they are all exactly the same,and are all in the same directory then I would use VBA to saummarise the files, if it is coded correctly then there is no reason that the code should be prone to error.
As for adding files, you could always check for the number of files in the directory and then iterate through them (minus one if the summary file is in the same directory).




_________________
Regards,

Gary Hewitt-Long
This message was edited by gplhl on 2002-10-22 15:25

 

[Barry Katcher]

Try the links first. If you can manage to keep both the summary and all the detail workbooks in the same sub-folder, it will shorten each part of the cell formula to

=[Workbook]Sheet1!A1+ etc., etc.

And, since you say all the detail pages and the summary page are the same range/configuration, you only need to create the formula once, in cell A1, and then copy it to the entire range.

If they are in different sub-folders/directories, if the workbooks themselves are linked to other sites or if they are password protected, it might be better to copy/paste values. I had to go that way on a 20-department budget with different passwords for different users that I had to roll up into a summary page.