I'm struggling with the following scenario:
Scenario
I have an xlsm which works as a dashboard for pulling in data on multiple Projects, each Project having its own xlsm as a highlight report.
Each xlsm Highlight Report is in a seperate folder based on its Project and the folders and the 'Dashboard' xlsm are on a single server on our corporate network.
Currently, because of #SPILL! from merged cells that need to be linked in order to be reported on, the data is pulled into a separate Sheet (Sheet1) and then the first cell in the link is referenced by the 'Dashboard' sheet for reporting.
33 cells are pulled from each Highlight Report xlsm into the 'Dashboard' - 14 Projects, therefore 14 different Highlight Report xlsm files are being referenced; also therefore 462 cells are being referenced.
Issue
Even if all the separate workbooks are open, the cells in the 'Dashboard' do not update on open. Each status has an error associated.
The only way to make this work is, to each time I open the 'Dashboard', change the source back to the right Highlight Report xlsm, it then updates all the cells in the 'Dashboard'. However as soon as the 'Dashboard' is saved, closed and re-opened, the links Error again.
Things I have tried
1. Changing the workbooks from xlsm files to xlsx - making sure the Dashboard and Highlight Reports are the same type of file.
2. Making sure that the Dashboard updates automatically
3. Checking the calculation options
I'm starting to lose faith at this stage and I'm concerned because the 'Dashboard' is client facing and the information just isn't connecting.
Any and all advise would be warmly welcomed.
Scenario
I have an xlsm which works as a dashboard for pulling in data on multiple Projects, each Project having its own xlsm as a highlight report.
Each xlsm Highlight Report is in a seperate folder based on its Project and the folders and the 'Dashboard' xlsm are on a single server on our corporate network.
Currently, because of #SPILL! from merged cells that need to be linked in order to be reported on, the data is pulled into a separate Sheet (Sheet1) and then the first cell in the link is referenced by the 'Dashboard' sheet for reporting.
33 cells are pulled from each Highlight Report xlsm into the 'Dashboard' - 14 Projects, therefore 14 different Highlight Report xlsm files are being referenced; also therefore 462 cells are being referenced.
Issue
Even if all the separate workbooks are open, the cells in the 'Dashboard' do not update on open. Each status has an error associated.
The only way to make this work is, to each time I open the 'Dashboard', change the source back to the right Highlight Report xlsm, it then updates all the cells in the 'Dashboard'. However as soon as the 'Dashboard' is saved, closed and re-opened, the links Error again.
Things I have tried
1. Changing the workbooks from xlsm files to xlsx - making sure the Dashboard and Highlight Reports are the same type of file.
2. Making sure that the Dashboard updates automatically
3. Checking the calculation options
I'm starting to lose faith at this stage and I'm concerned because the 'Dashboard' is client facing and the information just isn't connecting.
Any and all advise would be warmly welcomed.
