Implementation Approach

kgw2511

New Member
Joined
Mar 11, 2015
Messages
3
I’d be grateful for suggestions on the best approach to implement the following:

I need to collate 14 data items from around 35 organisations each month into one “master” worksheet, which in turn uses the data in calculations and charts.

I envisaged having the master workbook located one level above 12 monthly folders in my file structure and that each month the “input” workbooks would be saved to the relevant month folder but with the same name e.g. the workbooks from organisation AAA would always be saved as file_A.

So far when setting up the master workbook with links to the first month’s input workbooks I have been experiencing the “Update Values” dialogue box issue because the workbooks do not yet exist.
I have tried several suggestions to suppress this dialogue box, such as:
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
ThisWorkbook.DisplayAlerts = False
but nothing works.

At month-end I envisaged:
1. copying the links to the current month folder
2. editing these links to change the folder in the formulas to the following month’s folder
3. breaking the links to the current month

but this will cause two problems:
1. There will be 490 “Update Values” dialogue boxes displayed.
2. There will be duplicate file names in the “Edit Links” dialogue box, making the task of breaking links to the closed month trickier than I’d hoped.

I have looked at the workarounds to combat the inadequacies of using INDIRECT on closed files, but couldn’t get any of those to work. Also, I would not be able to use an Add-in.

Is there a more elegant and robust way to approach this implementation (using Excel 2010 and Windows 7), and if so, what?

Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,136,274
Messages
5,674,770
Members
419,525
Latest member
helensesc

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