Dynamic Chart to not include blanks

Hi all,

I am sure this question has been asked in here before, apologies if so as I couldn't find it.

I am essentially pulling through data into a new sheet with the aim of the graph being dynamic.

My issue is I can`t exclude the blanks. Any thoughts on how to get round this?

Thanks,

Scott

This really depends on what the problem is. If you could attach a sample worksheet I bet you would get more accurate help. If your problem is handling 0s in a dynamic range, try this:

I have done this before, and it is hard to get completely around. One way to go about it is to set your labels in the chart to show "" if their corresponding cells are empty.

If you have labels in A1 and data in B1, you could say,

=if(B1="", "", "Label 1")

Then in B1

=if(your formula = 0, "", your formula)

You can then set the number formatting to exclude 0s.

I`ll give this ago, unfortunately I have confidential data so can`t upload this, but have knocked together an example.

dyamic chart example.xlsx

In my actual document there is an IF which shows only what my true criteria is.

I have tweaked it to better show what I`m doing.

dyamic chart example.xlsx

Where the zero's are, even If I make them appear blank, they still populate the chart.

I see what you are saying. Jeff may be able to speak to this better than me, but I have always wondered if this was possible:

If you look at the chart, the formula is:

=SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$10,Sheet1!$C$2:$C$10,2)

Is it possible to run a COUNTIF on the series and set a dynamic range for the =SERIES function itself where the COUNTIF is the column number?

Like...
=SERIES(Indirect(Sheet1!$C$1, Sheet1!&C&COUNTIF(A2:A10 <> 0)) but with much better syntax?

I am not sure if this is possible, but its always something I've wondered.

I think andy just answered my question before I asked it...Impressive.

Thanks Andy, Will try and implement this on my data. Cheers Jake aswell for your help.

This is exactly what I needed!!


Thanks for the help