nige
11-05-2015, 02:33 PM
Hi all
Relative newcomer to VBA, and this is my first post... so, here's my problem.
I have created an excel .xlsm dashboard which links to cells within (source) .xlsx workbooks, and when I open the master dashboard and select the update option, all cells are then updated as per the relevant source workbook. In effect the master dashboard is used to extract pertinent cells from within the source files and display in a particular format.
However, if I then open a source file (whilst the master dashboard is open) and change the data in the source file, the master dashboard cells are automatically updated, which is not what I want.
To explain why this is a problem - when I open the master dashboard and update the cells from the (source workbooks), one of the cells I'm updating originates from a cell with the formula TODAY(). This works well if I just update the master spreadsheet, but when I open the source workbook at the same time (because the TODAY() cell then updates to today's date in the source workbook) subsequent changes are migrated into the master dashboard , which is not what i want.
What I want to do:
1. Open the master xlsm dashboard and update (links) to source workbooks.
2. Disable future updates from occurring.
I have some limitations:
- I cannot alter the source files to .xlsm to enable the date to be saved upon exit, because there are hundreds of these spreadsheets used, which would be a nightmare to re-work.
- I cannot disable calculations in the master dashboard once it has been updated because I need the calculations to work, so I can perform macro filtering.
- enable the above updating (requirements) without relying on other dashboard users changing excel Options settings - i.e. I'd like to keep all settings 'within' the workbook.
Hope this is [reasonably] clear.
Nige
Relative newcomer to VBA, and this is my first post... so, here's my problem.
I have created an excel .xlsm dashboard which links to cells within (source) .xlsx workbooks, and when I open the master dashboard and select the update option, all cells are then updated as per the relevant source workbook. In effect the master dashboard is used to extract pertinent cells from within the source files and display in a particular format.
However, if I then open a source file (whilst the master dashboard is open) and change the data in the source file, the master dashboard cells are automatically updated, which is not what I want.
To explain why this is a problem - when I open the master dashboard and update the cells from the (source workbooks), one of the cells I'm updating originates from a cell with the formula TODAY(). This works well if I just update the master spreadsheet, but when I open the source workbook at the same time (because the TODAY() cell then updates to today's date in the source workbook) subsequent changes are migrated into the master dashboard , which is not what i want.
What I want to do:
1. Open the master xlsm dashboard and update (links) to source workbooks.
2. Disable future updates from occurring.
I have some limitations:
- I cannot alter the source files to .xlsm to enable the date to be saved upon exit, because there are hundreds of these spreadsheets used, which would be a nightmare to re-work.
- I cannot disable calculations in the master dashboard once it has been updated because I need the calculations to work, so I can perform macro filtering.
- enable the above updating (requirements) without relying on other dashboard users changing excel Options settings - i.e. I'd like to keep all settings 'within' the workbook.
Hope this is [reasonably] clear.
Nige