Dynamic Control in a graphic

[andrefraga]

Good Morning,




The context is as follows: A Project Physical Progress Curve.
The project consists of four Sequences (1,2,3 and 4) that include several activities, each sequence has its own curve (4 curves) and there is a general curve of the project. All curves are ready as you can see in the attached spreadsheet.




What I need to do is create a dynamic control (check box, combo box, does not matter at all) and then also enter slicers on the chart so I just choose what I want to show (Sequence 1, 2, 3 or 4 or General curve) and appear on the graphic.


Here's a link to access the spreadsheet:

https://drive.google.com/open?id=0B3oW9TEhhAioeU8xVE9lVkZRRFU

Thanks,

André.

 

[Gerald Higgins]

Hi.

I'm not going to look at your linked file.

But making charts display different data, dependent on something like a checkbox, is straightforward.

Here's one way, there are others.

Let's consider a simplified situation where you have two very small data ranges that you want to chart -

Range A......Range B
1................1.5
2................1.4
3................1.6

Set up a single cell somewhere convenient - next to the chart itself may be good, can be on a separate tab from the data.
Let's say you use cell C1.
Use data validation to limit entry into that cell to "A" or "B" (in your case this would be 1, 2, 3, 4 or "General")

Back with your data, use a helper column to pull data across from the main data, dependent on what choice you make in cell C1.

For example,
=if(C1="A",+A2,+B2)

If you have many data ranges, it may be easier to use a lookup formula instead of the above.

Then, make your chart data series use as its source, the helper column you have just created, and not the original source data.

And you're done.

 

[Gerald Higgins]

Also asked here (I think !)

http://www.mrexcel.com/forum/questions-other-languages/931439-gr%E1fico-com-controle-din%E2mico.html

 

[andrefraga]

Yes, posted there too!!
I'm from Brazil, so posted in Portuguese ;P


In addition, I did what you proposed using "if", and worked fine. Thanks!
I already was on the plan of the "helper column", but the main diference is that I was trying with "MATCH" an "INDEX" formulas, which is a little more optimized than "IF". I'll keep trying and let you know if i reach a solution with these two or any other optimized way.
I also did, in addtion of what you proposed, the corelation with the box of selection and the graphic and it's working fine too.


Thanks for the help!


André.