Dynamic Chart Comparison Based on 3 Drop-down Selection

Psionicrnd

New Member
Joined
Jan 3, 2014
Messages
9
Hello,

Is it possible to create a dynamic chart to compare data based on month wise along with specific item type based on selections made from drop down menus?

For example:-
A column has Months
B, C, D, E has different Items info.

So what i am trying to do is when i select 2 different months from adjacent drop downs along with the Item type the chat should show the comparison.

Also posted on this forum but havent got any response yet. Plz have a check on Excel attached in the below post
Dynamic Chart Comparison Based on Drop-down Selection
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Let's say your data is in A5:E18 (12 months), with row 5 having labels.

Make the first dropdown link to cell F2 and the second to F3.

Repeat the headers in B5:E5 in B1:E1.

Select A2:E3 with A2 the active cell, type the formula

=INDEX(A$5:A$18,$F2)

then hold CTRL while pressing ENTER so all the selected cells pick up the formula.

Make your chart using the range A1:E3.
 

Psionicrnd

New Member
Joined
Jan 3, 2014
Messages
9
Hello Sir Jon thanx for your reply. Well you said to create two drop-downs in F2 and F3...I wanna ask were you referring to two months or one month and one item type or something else. Actually i am trying to create a chat based on 3 drop downs:- 2 drop downs that will show different months and 1 drop down to show item type for comparison.

Secondly you mentioned about index with related to F2, so here no use F3 dropdown? sorry i am so confused as i am not an excel genius. Could you please go through the Excel doc attached i the above link to understand what i mean about 3 drop downs. Plz help
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Those two dropdowns select the two months for your comparison. Your title said 3 dropdowns but your description only mentioned two.

Now a third one to show item type, that makes it trickier.

So if A5:E18 is okay for your data, we'll set up the item type data below this, and link A5:E18 to the selected item. Set up everything above row 5 as in my earlier description.

Put the data for:
item type 1 in A25:E38
item type 2 in A45:E58
item type 3 in A65:E78
item type 4 in A85:E98
etc.

So every 20 rows we insert data for another item type

Each range looks the same, header row in row 25, 45, 65, 85, etc., and months in column A.

Put a third dropdown for the item type and use cell G3 as the link cell.

Select A6:E18, with A6 being the active cell, and enter this formula:

=offset(A6,$G$3*20,0)

So for item type 1, G3=1, and the above offset reduces to =A26. For item type 2, it become =A46, etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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