We have a situation where we need to roll up a varying number of detail financial workbooks. I want to provide some automation for the ‘roll-up’ workbook. The workbooks containing detailed information may vary from 3 – 60 and reside in differing subdirectories. The worksheets to be summarized from each workbook are identical in layout and name.
Our current ‘roll-up’ workbook has six ‘generic roll-up’ worksheets with summing formulae containing ‘psuedo-links’ to 10 workbooks each. This was done to limit the size of the formulae since they contain the fully qualified path and name for each of the 10 workbooks. These six sheets in turn are consolidated within one master sheet. The person building the workbook does a manual ‘Edit/Links’ process to change the psuedo-links to the actual filenames.
I thought perhaps I could use a form with the Refedit control to aid the user in selecting the workbook names. I would populate some predefined ranges in a worksheet with the chosen workbook names and then run a macro that would build the formulae for each of the six interim sheets using the names from those ranges.
Any highlevel design advice or better alternative approaches?
