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
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
