Graph Data Source Question, not for the timid!

[Bill_Biggs]

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

 

[Fazza]

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]

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]

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

 

[Bill_Biggs]

Thank you all for this great advice! I truly appreciate it!