Graph Data Source Question, not for the timid!

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
I have data for a graph located between C58 and F97, for 40 rows of data total. Sometimes all 40 rows are populated and other times there are only 5 or 6 lines of data total. I have two questions here:

1) The spreadsheet has a macro that populates the cells in question and creates the graph. How can I tell the macro just to consider the populated rows and ignore the empty ones, when it is creating the graph?

2) Once the graph is created and the macro is finished, I want to give the user the option of excluding some of the lines of data from the graph if they suspect the data is faulty. So to do this, I have run a column (G58 to G97) down next to the columns of data and set them up so that if you click on one of the cells in the G column, a check mark appears and conditional formatting grays out the row in question. Is there a way to make the graph sensitive to the fact that that row is no longer to be considered?

Thanks ahead of time.

Bill Biggs
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi Bill.

I think one way you can solve this using the fact that #N/A entries do not plot.

So for (1), with the macro populating the cells, it can populate "=NA()" for the cells without values.

And for (2), change to a formula that links to the check box or a TRUE/FALSE field. So, "=IF(true/false field,value,NA())" And the conditional formating could link to the same TRUE/FALSE field.

(This could done without VBA - pre-set up in a range of cells, which link to the range populated by VBA, and the charts linked to those cells.)

HTH, Fazza
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Also, you might consider creating a dynamic named range to use as your source data for the chart. That way, the chart would automatically adjust for more or less data in the 40 row range, automatically, by making the lower limit of the range equal to the row below which there is no additional data. I don't do code, this solution would not require any. HTH. Larry
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Sheet1!Dee=OFFSET(Sheet1!$C$58,0,1,MATCH(MAX(Sheet1!$D$58:$D$97)+1,Sheet1!$D$58:$D$97,1),1)
Sheet1!Eee=OFFSET(Sheet1!$C$58,0,2,MATCH(MAX(Sheet1!$E$58:$E$97)+1,Sheet1!$E$58:$E$97,1),1)
Sheet1!Eff=OFFSET(Sheet1!$C$58,0,3,MATCH(MAX(Sheet1!$F$58:$F$97)+1,Sheet1!$F$58:$F$97,1),1)
Sheet1!Labels=OFFSET(Sheet1!$C$58,0,0,MATCH(MAX(Sheet1!$D$58:$D$97)+1,Sheet1!$D$58:$D$97,1),1)
This seems to work dynamically with newly added and removed data.
Going to bed now! Larry
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

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
Top