Form Controls that decide the Chart content

[tlacloche]

I have attached a file, so please refer to that.

I would like to select either "Sales", "Profits", or "Costs" from the drop down menu in Q18 AND...
then select either "Total", "Jan-June" or "Jul-Dec" from the radio buttons underneath...
giving me a copy of that column in the N column.
So for example, i select "Sales" and "Jul-Dec", I will get a copy of column G in column N.
I can then have a ready made chart made up of Column A (the X-axis) and Column N (the Y-axis).

So the idea is so the chart can change based on the options you select that you want to view. I could then select "Total" from the radio buttons and my chart will now show column H in column N thus the Y-axis on the chart.

I also would like to have the option of the tick box "Last Years Avg" to be used in column O so my chart can have two series.
So if "Sales" is selected with "Total" AND the "Last Years Avg' box is ticked, the chart will display both column H and I.
But if i untick the box, the series disappears from the chart.

I need help on what formulas to place in column N and O for this to work.
Thank you

[DBY]

Hello
Take a look at the attached reply. Does this do as required?

DBY

[tlacloche]

thanks DBY.
one thing i would like done differently please (if possible):

When "Last Years Avg" is unticked, is there a way for the chart not to display the legend. But when it is ticked, the "Last Years Avg" legend is visible.

thanks again

[DBY]

Glad you found it useful. In answer to your further questions.

The chart can be placed and sized wherever you wish. Just Cut/Copy Paste and resize.

If you use this formula:

Please Login or Register  to view this content.

and link the chart title to the cell in which it's contained, the tiltle should be dynamic.

Unfortunately, I believe the only way to dynamically remove/add the legend is to use VBA.

Just noticed you've edited your last post whilst I was writing this, so the first two I assume you've solved, sadly the last one is the problem.

DBY

[tlacloche]

Yeah I solved the first 2 myself so I edited them out.
As for the one still visible, you say VBA is needed (id rather not).
I guess Ill have to make do.

Thank you so much for your help.