Line chart to zero
 

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

--
Message posted via http://www.officekb.com

Line chart to zero
 

Hi,

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

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

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"BNT1 via OfficeKB.com" <u19326@uwe wrote in message
news:8a471ddc13612@uwe...
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

--
Message posted via http://www.officekb.com

Line chart to zero
 

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

regards

Andy Pope wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200809/1