Identify active cell and use the column to add formula to another cell

I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.

I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.

What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.

Hopefully that is not too confusing.

In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].

I am new to Excel VBA (Usually Access) so any help would be appreciated.

Thanks. Jonathan

No code required. Just amend your formula.

B11: =IF(COUNT(B5:B10)=0,NA(),SUM(B5:B10))

Originally Posted by JonPugh

Andy, thanks for the reply.

However, this shows the #N/A error in the cell. Any idea how I can show this as blank?

Thanks
Jonathan

To show the #N/A as Blank, you can use Conditional Formatting to "hide" the value.

E.g

Select the range and then go to Format|Conditional Formatting

Select Formula Is from the 1st drop down and enter formula

=ISNA(B11)

where B11 is the top most cell in the selection you are highlighting.

Click Format

and from the Font menu, choose colour to match the background cell colour.

Clikc Ok

Click Ok to finish.

Either use another range for the chart data source. You can hide this if you unset the plot visible cells only option.

Or use conditional formatting to test cell for NA and set font to same colour as cell fill.

As far as the charts are concerned #N/A is the only way to get a cell that contains information to not plot. As all non numeric values, other than #N/A, are treated as zero.