Forum Discussion
Data Table Referencing Data Table
Hello,
I currently have a model with multiple regions that can be selected. I use a data table to cycle through the regions to look at the data on a national basis all in one place. I also use this table to aggregate regional scores to look at the national score.
I now have 10-15 portfolios comparing projects that impact my regional and national scores. I want to create another data table that compares the national scores resulting from each portfolio. There are other metrics in this table as well but none referenced from the other data table.
My portfolio data table is not updating the national scores for each portfolio, just showing the score from the most recently run data table. Does anyone know how I can get excel to re-run the regional data table for each portfolio? I would love to avoid manually running through each portfolio for the national score, as my team will likely be making tweaks and updates over the week.
Thank you!
2 Replies
- I know this all makes perfect sense to you, but from an outside person looking in, this is still unclear. I will take a stab at some of the unknowns here and in particular, it sounds like each of these Models / Tables / portfolios are in separate files and that each is using a direct reference to cells/tables/ranges/names in the other files? Then again maybe you are using Power Query but because you have A query B and B query C your problem is that A query B doesn't trigger B to query C? This sort of issue may require either a restructure of the workbooks (i.e. prevent multiple layers of files) or a macro that can open and refresh all the data files in the background. Can you confirm and further explain the situation?
Hello,
Thank you for your response. The models are all in the same excel spreadsheet. There is no power query or referencing other files or databases.
The model itself which takes inputs and calculates outputs is in a separate sheet, but all of the data tables are in the same summary sheet. I have data table A, which runs each region through the model and gets a variety of outputs, lets say one is the sum of sales.
Then in data table B, I run a variety of portfolios looking at different decisions that can be made across the regions. (Think: I want to open 10 stores in the country, in a variety of regions I have options for where to put, how to size, etc.) Each portfolio has a different impact on the results of data table A because of placement of the stores. I want to be able to pull the national sales total for each portfolio of stores.
The issue is that running data table B, does not trigger the run of data table A to re calculate the sum for each portfolio. Is it possible to make this happen?
Thank you!
