Autoupdate Charts

  • Thread starter Thread starter Mathew Bennett
  • Start date Start date
M

Mathew Bennett

Good Evening All,

I am hoping that someone here will be able to help me out of a niggling problem
I have searched Google, but found no solution to my niggle.

I have created a Auto Updated LINE Chart, using named ranges utilizing the OFFSET function,
to look at a selected set of data, - eg below:

A B
Month Value

01 100
02 150
03 200
04 150
05 250 etc,etc...

This works fine, as the chart updates as dates/values are added/changed/deleted.

However, the snag being that, the Coulmn B values are a calculated result from other cells,
(with the result being "" (blank) if source cell is blank, but the chart reads this cell as 0 (zero),
and the chart produces a line down to zero, when I would prefer the line on this part of the chart not to appear.

I hope I have explained this well enough, and hope that this peculiarity may be solved.

Thank you
Mathew
 
Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank,
and Excel offers no BLANK() function.)

- Jon
 
Hi Jon
Smashing thankyou, it works.
As an extra though, how can I force the cell to dispaly "" (blank), when #N/A appears.
I have tried additional If statements (circular refs) & conditionally formatting, but to no avail.
Cheers for your input.
Yours,
Mathew
Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank,
and Excel offers no BLANK() function.)

- Jon
 
Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank
 
Hi Jon
Smashing thankyou, it works.
As an extra though, how can I force the cell to dispaly "" (blank), when #N/A appears.
I have tried additional If statements (circular refs) & conditionally formatting, but to no avail.
Cheers for your input.
Yours,
Mathew

Mathew -

Change the formula so it returns NA() instead of "". ("" is not blank,
and Excel offers no BLANK() function.)

- Jon
 
Hi Guys,
Great, thank you.
Between you both, you gave me exactly what I needed.
Cheers again, many thanks,
Mathew

Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank
 
Hi Guys,
Great, thank you.
Between you both, you gave me exactly what I needed.
Cheers again, many thanks,
Mathew

Hi Mathew
To force a "blank" cell use conditional format: Enter the following
formula as condition for cell A1:
=ISNA(A1)
Use white as text color

HTH
Frank
 
Back
Top