Data Source in Excel Chart

[littlejilly]

Hello all,

I am looking to alter the data source in a chart that I have in excel.

Currently, on my "Model Inputs" tab, I have a data validation in cells C49 and C51 where you can only have an X in one of those cells.

If cell C49 is checked, then the chart (titled - "ReimImpact") on the "Summary" tab will use the data also found on the Summary tab in cells (series1: N41, N49, N57, N65, N73, N81 ; series2: O41, O49, O57, O65, O73, O81). Alternatively, is cell C51 on Model Inputs is checked instead, then the data source will be cells (series1: N43, N51, N59, N67, N75, N83 ; series2: O43, O51, O59, O67, O75, O83).

Any suggestions on how to write the code and where it should go would be greatly appreciated.

Thanks,

jillian

 

[Jon Peltier]

Okay, first let's make the chart data easier to use. Set up the cells in I3:I8 to link to N41, N49, N57, N65, N73, and N81; in J3:J8 to link to O41, O49, O57, O65, O73, and O81; in K3:K8 to link to N43, N51, N59, N67, N75, and N83; and in L3:L8 to link to O43, O51, O59, O67, O75, O83. [Use different ranges if necessary, but use a dedicated staging area, or you will go insane creating and maintaining the chart.]

In G1 put this formula which returns TRUE or FALSE:
='Model Inputs'!C49="X"

Select G3:H8, entered the following formula, and used Ctrl+Enter to fill the range with this formula. The formula fills G3:H8 with either the values in I3:J8 or with K3:L8.
=if($G$1,I3,K3)

Create the chart with the data in G3:H8.

Now whenever the user enters or removes an "X" in cell C49 of the Model Inputs sheet, these simple formulas will easily and instantly update the chart.

Enhancements:

1. You could hide the entire range of links (G1:L8) behind your chart.

2. It would be nicer for your users if you use a checkbox or two option buttons from the Insert Form Controls dropdown on the Developer tab. It's way easier for someone to check a box than to have to continually edit cells.

With a checkbox, the linked cell will be either TRUE or FALSE; with option buttons the linked cell will be either 1 or 2. The linked cell need not be right near the controls, they can even be on the Summary sheet (behind the chart if you like, with the other linked cells). Adjust the formula in Summary!G1 accordingly.

 

[littlejilly]

works like a charm. thank you for enhancement recommendations as well.