Chart to ignore zero values - can it be done

**nigelog** · 06-27-2008, 05:50 AM

Hi

the attached sample has a chart which monitors values sourced from named ranges. This in turn is populated from links from a front end sheet.(Sheet1)

My problem is that as there are 12 refence points included in the chart output, all not yet full, the chart reads the next reference as a zero and dives off the chart.

I would like it to ignore the zero refence so the chart line stays in the scale at the last reference value.

I tried removing the "0" values in Sheet2(the chart source) by using IF="" which cleared Sheet 2 of "0"s but the the chart would then only recognise the last row in the source named range.

If you view the chart you will see how they dive off and any assistance in correcting this would be greatly appreciated.

This project content is again the result of assistance given in this forum for which we here are grateful for.

**royUK** · 06-27-2008, 06:02 AM

have a look at Andy Pope's site

http://www.andypope.info/charts/piezeros.htm

**Andy Pope** · 06-27-2008, 06:05 AM

Text in chart is treated as zero

modify the formula on sheet2, for example.

c2: =IF(Sheet1!C3="",NA(),Sheet1!C3)

**nigelog** · 06-27-2008, 07:17 AM

Thanks Andy/Roy

Lines no longer dive off of the page.

Last item if possible. Can I allocate a named range to a VLOOKUP cell so I can select an individual truck from a drop down list and display only the specific row range for that truck. I have the drop down list if that is the waty to go all I need is for it to relate to the necessary range.

Any pointers appreciated

Rgds Nigel

**Andy Pope** · 06-27-2008, 07:23 AM

this recent post
http://www.excelforum.com/showthread.php?t=648214

**nigelog** · 06-27-2008, 09:45 AM

Hi Andy

many thanks for the pointers and chart now can select data from drop down list.

Last question - the example and my interpretation have the chart name source aimed at the row reference. ie row 2 to row 50 odd.

How can I adapt this to show the registration instead of this number. I am probably overlooking something simple and will keep trying but If anyone can suggest a fix, much appreciated.

Rgds Nigel

**Andy Pope** · 06-27-2008, 10:01 AM

Cell Sheet2!B56 needs this formula

=INDEX(B2:B53,A56,1)

Change chart data source to use Sheet2!B56 instead of A56

**nigelog** · 06-27-2008, 10:05 AM

Probably very Heath Robinson but have sorted the problem. Copied reg to a second column and adjusted the index lookup range.

Then in chart adjusted source of name to aim at the reg carried down to chart source line in Sheet2.

Works and it is Friday so I aint gonna try anything else fancier.

Many thanks for your help through this project and copy of finished application attached

Rgds Nigel

Probably be back again next week

**nigelog** · 06-27-2008, 10:18 AM

Hi Andy

did not see your post as I was posting myself. Your way was tidier so I used it. Sent the wrong attachment anyway as corrected version had been posted to server.

All finished attached

Again Many thanks Nigel