dynamic chart with drop down list for two charts on one graph

[mhghg]

Hi guys,
I like to use drop down list to select the charts, for example if I select DB/PS from cell D8,
It will update with the corresponding chart, but i dont have to draw all of them out
Please share some lights to my darkness.
Thanks,
Macy

 

[Hui]

Macy


Have a look at the attached file

Can you explain what ranges the lstGroup should be?

 

[Riyaz Ali]

Hope this is what you are looking for...

 

[mhghg]

Hi guys,
Thank you very much for all respond. Yesterday I tried to work out the solution by myself and I am coming up with this excel sheet. I am using combo box to control user selection. The thing I like is When I select the option in combo box, say DB/PS then it will auto select the %DB/PS in combo box 2. The combo box 2 will be grey out so it looks like only one drop down list.
Here is my version. Any idea, formula vba script ..?

 

[Hui]

I don't believe that you can change the color of Drop Downs as you want

You can reset it's value when the other drop down changes

add the following code to a Code Module in VBA

Sub DropDown4_Change()
[R16] = [Q16]

End Sub

 

[mhghg]

Hi Hui,
I have added the vba code into the module, but I when select the option from one drop down list the other does not change at all. Is that the way you did or did you do differently?
Macy

 

[Hui]

Sorry
Right click on the M3 Dropdown and Assign Macro
Select the DropDown4_Change macro

 

[Riyaz Ali]

Works great Hui... but the only question is why the need for a macro or for that matter the second drop box when the same result can be achieved by just using an "If" formula....

 

[Hui]

Try it
If you use a formula and then manually change the second Drop Down it will erase the formula

 

[Riyaz Ali]

Agreed... but is the second dropdown really needed?

 

[Riyaz Ali]

Attached is an example of what i am saying...

 

[Hui]

You only need the second drop down if you want to view different data

 

[mhghg]

You only need the second drop down if you want to view different data

Hi Guys,
Thank you very much. It works well. I also learn 3 things today.
1. Choose formula.
2. Simple vba
3. Offset function
4. Beautiful graphing technique

Both of Choose and Offset function are great, but I worry the users can mess around with the formulas unless we create and hide them in different sheet.

Have a good weekend
Macy

 

[Hui]

Your use of Named Formula on Sheet2 in your file is the recommended way to go if you don't want people playing with worksheet formulas

Although the Offset function is a great and simple function to understand it is Volatile and so is recalculated whenever anything on the worksheet changes

You are better to use the Index function which can return a Row or Column of data simply

see your file modified to use Index

 

[mhghg]

Hi Hui,
Thanks for your advice.
Macy