Forum Discussion

Copper Contributor

Apr 20, 2022

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

Silver Contributor

Apr 21, 2022

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?

densor
Copper Contributor
Apr 22, 2022
mtarler

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!

Resources