How do I retain blank cell status in an IF statement?
 

I have a monthly multiline graph to which I add data by day. The graph lines
terminate at the first empty cell (day) in the series, which is what I want.
However, If I use an IF statement to prime the cells dynamically the graph
lines drop to zero. I have not been able to achieve the desired result using
"", #NA or NA().

No worksheet cell that contains a formula can be truly blank. You have
already rejected the two closest approximations (#N/A and NA() are
equivalent).

The remaining options are

- delete the formulas in cells beyond actual data (you could use an
event driven macro to restore the formulas as data is added)

- Do something along the lines of
http://www.peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/ne...rts/index.html

Jerry

HWI wrote:

I have a monthly multiline graph to which I add data by day. The graph lines
terminate at the first empty cell (day) in the series, which is what I want.
However, If I use an IF statement to prime the cells dynamically the graph
lines drop to zero. I have not been able to achieve the desired result using
"", #NA or NA().

Thanks Jerry - the second site listed appears to be on the track I'm wanting.
Will try it!

"Jerry W. Lewis" wrote:

No worksheet cell that contains a formula can be truly blank. You have
already rejected the two closest approximations (#N/A and NA() are
equivalent).

The remaining options are

- delete the formulas in cells beyond actual data (you could use an
event driven macro to restore the formulas as data is added)

- Do something along the lines of
http://www.peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/ne...rts/index.html

Jerry

HWI wrote:

I have a monthly multiline graph to which I add data by day. The graph lines
terminate at the first empty cell (day) in the series, which is what I want.
However, If I use an IF statement to prime the cells dynamically the graph
lines drop to zero. I have not been able to achieve the desired result using
"", #NA or NA().