What is my best strategy here?????

ajmckenna

Board Regular
Joined
Oct 7, 2002
Messages
145
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

gplhl

Board Regular
Joined
Feb 23, 2002
Messages
150
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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.
 

Forum statistics

Threads
1,144,050
Messages
5,722,234
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top