Question about example of using drop down to vary three different variables; date, demo and metric

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
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.


1 ABCDEFGHIJKLMNO
2Total TimeTimes UsedPercent Use
3 DateP2+P<18P18-49P50+P2+P<18P18-49P50+P2+P<18P18-49P50+
4 Sep2.52.12.42.86.57.06.06.580.179.480.181.2
5 Oct2.82.02.33.07.07.25.96.682.579.583.083.2
6 Nov2.92.12.33.16.26.95.56.582.979.583.181.4
7 Dec3.02.22.23.46.47.15.36.581.480.183.580.6
8SHEET 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 ABCDEFGHIJKLMNO
2Total TimeTimes UsedPercent Use
3 DateP2+P<18P18-49P50+P2+P<18P18-49P50+P2+P<18P18-49P50+
4 Sep#NA2.1#NA#NA#NA7.0#NA#NA#NA79.4#NA#NA
5 Oct#NA2.0#NA#NA#NA7.2#NA#NA#NA79.5#NA#NA
6 Nov#NA2.1#NA#NA#NA6.9#NA#NA#NA79.5#NA#NA
7 Dec#NA2.2#NA#NA#NA7.1#NA#NA#NA80.1#NA#NA
8SHEET 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:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Hi Abuchanan,

would you happen to have a link your file (maybe dummify your data) on e.g. Dropbox/Skydrive? (One Excel file tells more then your whole long description ;).).

Koen
 

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
Would LOVE some help with this. I can even just send you the file if you want. I'll just cut it down a lot, since it is such a big file; but you can see what I have done. I've got it "semi" working... but it CERTAINLY is not efficient. I think I am using way too many named variables, and it's just not very stable. There are a few issues I'm about to give up on!!!!! How can I get you the file??? And thanks, thanks, thanks for the help!!!!!!
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
Hi Abuchanan,

as said, you could put in on a dropbox/skydrive/google drive folder and share the link here. The other option is mailing it directly to me: my forum name @hotmail.com will do the trick.

Cheers,

Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top