Variable X-Axis Chart to Auto-Populate with 2 Drop Down

[j2me5ku]

Hello,

I'm just learning about drop downs and offsets, and I would love your help in figuring this out.

Goal: Create a Line Graph with two drop downs that displays a variable amount of data based on the selected timeline along the X-Axis:

1) Select a category

2) Select a time period

There are 4 categories - Total, Mens, Womens, and Mens vs. Womens. Please note that Total is not the summation of Mens and Womens. Each category will display current and comparative time period data depending on the selected time period. Mens vs Womens will only display current data depending on selected time period.

The time period would be Year (broken down by months), QTR (current QTR broken down by weeks), and Month (current month broken down by days). Please note the fiscal year starts on Feb and the months for the quarter are broken out by a 4-5-4 week increments.

Progress: I've been able to create the drop downs but that is as far as I've gotten. I can't seem to wrap my head around writing a formula that would be able to display this information in a dynamic chart.

https://www.dropbox.com/s/xgyxu7ipg29ynqj/example.xlsx?dl=0

Your help would be greatly appreciated.

Thank you in advanced.

james

 

[Nebu]

Hi:

I guess you can do this using pivot tables and slicers, you can have separate slicers for Week, month, year , QTR and category and manipulate your graph accordingly.

Thanks

 

[j2me5ku]

Hi:

I guess you can do this using pivot tables and slicers, you can have separate slicers for Week, month, year , QTR and category and manipulate your graph accordingly.

Thanks

Thank you Nebu. I've looked into using slicers, but I don't like the fact you can select more than one category. Is there a way to restrict the slicer so you can only select one element at a time?

Also, I wanted to stay away from slicers because they take up space in a very limited dashboard I'm creating thus the drop down.

Any advice would be greatly appreciated.

 

[Nebu]

Hi:

I do not think you can restrict Multiple selection in slicers without using VBA. Your drop box file is not working, I forgot the layout of your file. The question in the your OP is achievable using name ranges I guess, again I have to see the file in detail to suggest any.

Thanks