I have a file set up as follows:
My Raw data has three tables side by side with different metrics, Total Time, Times Uses, Percent Used. Each Table has a row for the months, and three columns, one for each of three age groups.
<tbody>
</tbody>
I'm setting up a dashboard which shows all three metrics on one page in three different bar chart - one for each metric. I'm adding two drop downs, one that allows the user to pick the demo, and one that allows them to pick the start date. (My real data has many more months (rows) in it.)
At the end of each month, new data will be added.
I'm going to force the end user to always view the data/bar chart through the latest available data, in this case, Dec. Therefore, only the start date can vary -- the end date will always be the last date available.
I've received a lot of help on this, but still stuck at the end.
My first step is to create cal data which brings across only the demo data that the end user picks. I'm using IF statement in each cell below, so that if the Column Title matches what the end user picks, then the values fill in; if not, the cell gets a #NA.
In the example below, the end user has picked the demo P<18.
<tbody>
</tbody>
I've got this drop down to work. If I change demos, my three bar charts all change to reflect the new demo.
My problem comes in when adding the drop down for the start date.
I know I need to go to each chart, and do a SELECT DATA, and edit the series.
It brings up the Edit Series box:
I have the series name as:
='Calculated Data'!$AA$2 [NOTE: I copied the titles of the demos in Column AA1 to AA4 = therefore $AA$2 gives me P<18
Then I have to put in the Series Value... and this is where I am stuck.
On my Cal Data sheet, I manipulate the data around so that I ultimately have two cells, one which provides the start of the series and one which provides the end of the series. Say the end user picks the start date of Oct. As mentioned above, my end date is always the last month, in this case Dec.
In this example, I've got it so if the user picks Oct, and the end date is Dec, then on my Cal Data sheet, I would have cell X1 contain where to start (my example, Cell X1 would contain C5 for October/Total Time; Cell X2 would contain H5 for October/Times Used; and Cell X3 would contain M5 for October/Percent Used )--
Also Y1, Y2, Y3 contain C7, H7, M7 for the end dates.
However, I have no idea how to put this in the series formula/range.
I've tried combination of INDIRECT and tried OFFSET; I also used the Name Manager to name my start cells and end cells (TTStart=C5, TUStart=H5, PStart=M5... and TTEnd=C7, TUEnd=H7, PEnd=M7)... but still can't get this to work.
I've looked and looked on the forum, and also on google for an example like this and can't find something where they are doing this.
I've also talked to a few folks on here who have already helped me A LOT to get this far (they were answering specific question about this, and didn't tell me to approach the problem this way... that was my doing, which is probably COMPLETELY wrong and inefficient... so only blame me for that!)
If anyone even understands what I am saying and could offer some help, it would be great. I've been beating my head against the wall, and now I have a big old knot head!)
My Raw data has three tables side by side with different metrics, Total Time, Times Uses, Percent Used. Each Table has a row for the months, and three columns, one for each of three age groups.
| 1 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| 2 | Total Time | Times Used | Percent Use | ||||||||||||
| 3 | Date | P2+ | P<18 | P18-49 | P50+ | P2+ | P<18 | P18-49 | P50+ | P2+ | P<18 | P18-49 | P50+ | ||
| 4 | Sep | 2.5 | 2.1 | 2.4 | 2.8 | 6.5 | 7.0 | 6.0 | 6.5 | 80.1 | 79.4 | 80.1 | 81.2 | ||
| 5 | Oct | 2.8 | 2.0 | 2.3 | 3.0 | 7.0 | 7.2 | 5.9 | 6.6 | 82.5 | 79.5 | 83.0 | 83.2 | ||
| 6 | Nov | 2.9 | 2.1 | 2.3 | 3.1 | 6.2 | 6.9 | 5.5 | 6.5 | 82.9 | 79.5 | 83.1 | 81.4 | ||
| 7 | Dec | 3.0 | 2.2 | 2.2 | 3.4 | 6.4 | 7.1 | 5.3 | 6.5 | 81.4 | 80.1 | 83.5 | 80.6 | ||
| 8 | SHEET NAME: RAW DATA |
<tbody>
</tbody>
I'm setting up a dashboard which shows all three metrics on one page in three different bar chart - one for each metric. I'm adding two drop downs, one that allows the user to pick the demo, and one that allows them to pick the start date. (My real data has many more months (rows) in it.)
At the end of each month, new data will be added.
I'm going to force the end user to always view the data/bar chart through the latest available data, in this case, Dec. Therefore, only the start date can vary -- the end date will always be the last date available.
I've received a lot of help on this, but still stuck at the end.
My first step is to create cal data which brings across only the demo data that the end user picks. I'm using IF statement in each cell below, so that if the Column Title matches what the end user picks, then the values fill in; if not, the cell gets a #NA.
In the example below, the end user has picked the demo P<18.
| 1 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| 2 | Total Time | Times Used | Percent Use | ||||||||||||
| 3 | Date | P2+ | P<18 | P18-49 | P50+ | P2+ | P<18 | P18-49 | P50+ | P2+ | P<18 | P18-49 | P50+ | ||
| 4 | Sep | #NA | 2.1 | #NA | #NA | #NA | 7.0 | #NA | #NA | #NA | 79.4 | #NA | #NA | ||
| 5 | Oct | #NA | 2.0 | #NA | #NA | #NA | 7.2 | #NA | #NA | #NA | 79.5 | #NA | #NA | ||
| 6 | Nov | #NA | 2.1 | #NA | #NA | #NA | 6.9 | #NA | #NA | #NA | 79.5 | #NA | #NA | ||
| 7 | Dec | #NA | 2.2 | #NA | #NA | #NA | 7.1 | #NA | #NA | #NA | 80.1 | #NA | #NA | ||
| 8 | SHEET NAME: Cal Data |
<tbody>
</tbody>
I've got this drop down to work. If I change demos, my three bar charts all change to reflect the new demo.
My problem comes in when adding the drop down for the start date.
I know I need to go to each chart, and do a SELECT DATA, and edit the series.
It brings up the Edit Series box:
I have the series name as:
='Calculated Data'!$AA$2 [NOTE: I copied the titles of the demos in Column AA1 to AA4 = therefore $AA$2 gives me P<18
Then I have to put in the Series Value... and this is where I am stuck.
On my Cal Data sheet, I manipulate the data around so that I ultimately have two cells, one which provides the start of the series and one which provides the end of the series. Say the end user picks the start date of Oct. As mentioned above, my end date is always the last month, in this case Dec.
In this example, I've got it so if the user picks Oct, and the end date is Dec, then on my Cal Data sheet, I would have cell X1 contain where to start (my example, Cell X1 would contain C5 for October/Total Time; Cell X2 would contain H5 for October/Times Used; and Cell X3 would contain M5 for October/Percent Used )--
Also Y1, Y2, Y3 contain C7, H7, M7 for the end dates.
However, I have no idea how to put this in the series formula/range.
I've tried combination of INDIRECT and tried OFFSET; I also used the Name Manager to name my start cells and end cells (TTStart=C5, TUStart=H5, PStart=M5... and TTEnd=C7, TUEnd=H7, PEnd=M7)... but still can't get this to work.
I've looked and looked on the forum, and also on google for an example like this and can't find something where they are doing this.
I've also talked to a few folks on here who have already helped me A LOT to get this far (they were answering specific question about this, and didn't tell me to approach the problem this way... that was my doing, which is probably COMPLETELY wrong and inefficient... so only blame me for that!)
If anyone even understands what I am saying and could offer some help, it would be great. I've been beating my head against the wall, and now I have a big old knot head!)
Last edited:
.).
