Chart series display problem

[John Ross]

At the end of my chart I always have a vertical line that is going to 0.
That is caused because the cells that the chart draw contains formulas. The
outcome of the formulas is space but the chart sees them as zeroes.
What I would like to do is the use somthing like the formula below using the
indirect function inside a series function so I can always know which is my
last cell to draw that contains information:

=SERIES(Investment!$G$2,Investment!$B$31:$C$181,Investment!$M$31:INDIRECT(CELL("address",A162)),1)

Unfortunatelly it is not acceptible by Excel.

What can I use instead????

Tx
JR

 

[Gord Dibben]

John

Create a named dynamic range and use that in your Series formula.

Stephen Bullen has a downloadable workbook demonstrating this technique.

http://www.oaltd.co.uk/Excel/Default.htm

Scroll down to FunChrt1.zip and download.

Or check out Tushar Mehta's site on Dynamic Charts

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html


Gord Dibben MS Excel MVP

 

[John Ross]

Thank you.
I am going to check this.
JR

John

Create a named dynamic range and use that in your Series formula.

Stephen Bullen has a downloadable workbook demonstrating this technique.

http://www.oaltd.co.uk/Excel/Default.htm

Scroll down to FunChrt1.zip and download.

Or check out Tushar Mehta's site on Dynamic Charts

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html


Gord Dibben MS Excel MVP

 

[Andy Pope]

Hi,

Use NA() instead of "" in your formula. Use conditional formatting, if
necessary, to mask the #N/A's

Cheers
Andy