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

1. ## 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

2. ## Re: Identify active cell and use the column to add formula to another cell

No code required. Just amend your formula.

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

3. ## Re: Identify active cell and use the column to add formula to another cell

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

Thanks
Jonathan

4. ## Re: Identify active cell and use the column to add formula to another cell

Andy Pope has written this to give taht error message -- maybe this

=IF(COUNT(B5:B10)=0,"",SUM(B5:B10))

jiuk

5. ## Re: Identify active cell and use the column to add formula to another cell

JIUK,

This then puts me back in the position of it plots the "" as a zero on the chart.

Any other ideas?

Jonathan

6. ## Re: Identify active cell and use the column to add formula to another cell

Use Andys code -- it will show NA but not in Your chart

Have you tried / played?

Jack in the UK

7. ## Re: Identify active cell and use the column to add formula to another cell

Originally Posted by JonPugh

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.

8. ## Re: Identify active cell and use the column to add formula to another cell

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.

9. ## Re: Identify active cell and use the column to add formula to another cell

Thanks to you both. I have used th econditional formatting.

Jonathan

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1