Line chart to zero

[BNT1 via OfficeKB.com]

Hi
I am extracting data from Jan shhet into summary sheet, and from that summary
sheet creating a line graph
Formula in cell reads
=INDIRECT("'"&(B1)&"'!c5")). This returns a "0" .

The graph looks ugly dropping down to the zero when no data, so looked in
previous threads and now in cell formula reads
=IF(INDIRECT("'"&(B1)&"'!c5")="",(na),INDIRECT("'"&(B1)&"'!c5"))

When value in the Jan sheet is >0, the summary sheet is populated correctly,
and the graph shows the line ok, however, when no value is in Jan sheet, I
get #NAME? error, and line still shoots to zero

any idea's why? or how i can stop the line shooting to zero, unless 0 is in
the origonal cell

Help is appriciated

regards

Brian

 

[Andy Pope]

Hi,

That would be the function NA() rather than na in brackets.

=IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'"&(B1)&"'!c5"))

Cheers
Andy

 

[BNT1 via OfficeKB.com]

Thanks Andy, worked a treat. This will teach me to pay more attention to
detail NA() instead of (NA)

regards

Hi,

That would be the function NA() rather than na in brackets.

=IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'"&(B1)&"'!c5"))

Cheers
Andy

Hi
I am extracting data from Jan shhet into summary sheet, and from that

[quoted text clipped - 21 lines]

Brian