Hello! Can anyone tell me if the below scenario is possible and suggest some formula refinements?
Could this be done with a data slicer? or scenario? I have spent hours researching.
GOAL: My goal is to automate as much as possible, a dashboard and be able to change any data (#) manually, so I can model different scenarios month x month and see the subtotal formula update.
Thank you in advance for being excel wizards with a Chandoo attitude!
Notes:
1. My first table is dynamic - Helper Column A is used for Table 1. (Cells B2-I2 are brought in from 'raw' tab. The month cells at top, are also brought in from 'raw' table as headers)
2. The 2nd table I created for illustration purposes (because I need help). I may have similar 3rd or 4th Table.
3. Yellow field is my filter criteria. Use that field to bring in data from a different tab as shown in formula. All data is in ONE tab, but not sorted.
4. Blue Fields are calculations that should be provided at the bottom of the filtered data as shown - I want these at the bottom of each table
5. I want to be able to EDIT the values that I imported for modeling purposes (This is why the true =filter formula may not work)
6. The counts of datapoints change, each table may have 6 rows, or 15 rows, etc.
Could this be done with a data slicer? or scenario? I have spent hours researching.
GOAL: My goal is to automate as much as possible, a dashboard and be able to change any data (#) manually, so I can model different scenarios month x month and see the subtotal formula update.
Thank you in advance for being excel wizards with a Chandoo attitude!
Notes:
1. My first table is dynamic - Helper Column A is used for Table 1. (Cells B2-I2 are brought in from 'raw' tab. The month cells at top, are also brought in from 'raw' table as headers)
2. The 2nd table I created for illustration purposes (because I need help). I may have similar 3rd or 4th Table.
3. Yellow field is my filter criteria. Use that field to bring in data from a different tab as shown in formula. All data is in ONE tab, but not sorted.
4. Blue Fields are calculations that should be provided at the bottom of the filtered data as shown - I want these at the bottom of each table
5. I want to be able to EDIT the values that I imported for modeling purposes (This is why the true =filter formula may not work)
6. The counts of datapoints change, each table may have 6 rows, or 15 rows, etc.
Attachments
Last edited: