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.
have a look at Andy Pope's site
http://www.andypope.info/charts/piezeros.htm
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Text in chart is treated as zero
modify the formula on sheet2, for example.
c2: =IF(Sheet1!C3="",NA(),Sheet1!C3)
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
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
Cell Sheet2!B56 needs this formula
=INDEX(B2:B53,A56,1)
Change chart data source to use Sheet2!B56 instead of A56
Sorted Chart Name As Registration
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks