Data Source in Excel Chart

littlejilly

Board Regular
Joined
Sep 8, 2011
Messages
168
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0

Forum statistics

Threads
1,223,284
Messages
6,171,179
Members
452,388
Latest member
Lorenzo_Barry

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
Back
Top