• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interactive/Dynamic Chart

maradykstra

New Member
Hello,

I am building an interactive/dynamic chart. I want to show the performance of sales to my staff. Let't say they are selling chocolate and vanilla. I want to show how well they are doing each week within the month. I've created a drop down with all the months before where you pick the month and the data changes according to that month (used the index function for that). However, I want to pick the month and it displays all the weeks within that month so my staff can see their performance each week within that month. I'm having trouble referencing the months to the specific weeks to the month so I can show it on the dynamic chart. I hope that makes sense.


Any help will be greatly appreciated.


Thank you.
 

Hui

Excel Ninja
Staff member
JaElle

Understand what your trying to do but without specifics its very hard to assist.

can you post your data somewhere?
 

maradykstra

New Member
Here's my data for two months but I'll have to do it for all the months later:


Chocolate Vanilla

January Week 1 50 100

January Week 2 25 89

January Week 3 67 45

January Week 4 15 25

Febuary Week 1 37 125

February Week 2 20 45

February Week 3 80 75

February Week 4 55 39


My drop down box reference is:

January

February

March

April

May

June

July

August

September

October

November

December


I think where I'm having rouble is that I want my drop down list month to return the right week and data so when I pick the month then it will show the weeks that correspond to the month. I'm thinking I have to do an offset formula?
 

kchiba

Active Member
Hi JaElle,


You can use build a table with the Weeks across and place the month as follows


...... Week1 Week2 Week3 Week4 Week5

Jan


and then use a SUMIFS formula as follows


=SUMIFS($C$2:$C$10,$A$2:$A$10,$A17,$B$2:$B$10,B$16)


The formula is based on the C2:C10 - Choclate; A2:A10 Months; B2:B10 the Weeks


The table is in A16:F17 and the formula is in B17
 
Top