Trouble with dinamic chart - Blank cells from if statement

Hi all,

Hope you can help me with this one.

I have a range of 20 rows that is populated by if statements. say A2:B20

Based on this if statements there will be a row where the data will stop and after that, all rows it will be blanks til row 20.

So for example if there is data in Range A2:B10, the remaining A11:B20 range will be blank. (Blank by if statement (;“”)

I am trying to build a graph on the visible data alone so I tried the named range approach using the
OFFSET/COUNTA combination to create the named ranges.

But I got a problem with this one.
Since the blanks derive from a If statement, the counta formula doesn´t take those as blanks and will display it as data, defeating the whole purpose.

Is there another approach I can use to make a dynamic chart? I tried the search but could not find anything.

Thanks

You could perhaps use COUNTIF <>"" rather than COUNTA.


Regards, TMS

Thanks TMS, but that does not seem to solve it.

Before I test on the graphs I create the Offset/Counta formula and then test it by doing a validation on a cell selecting "List" and then using the named range.

If the list then shows the blanks there, it means its not ignoring them as it should.

See attached sheet with the examples of both named ranges using both methods (Counta and Countif) you can see the results on cells H2 and H3. They both display the blanks on the list.

Maybe I am writing something wrong? If not, is there another methos to do the graph to ignore the "blanks"?

Thanks

At this point I would be happy to find a workaround. Any ideas?

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,"> "))


I tend not to use OFFSET because it's volatile. INDEX usually gives me what I want. Notice the space character in the greater than comparison, "> ".


Regards, TMS

Thanks TMS.

That formula works for the first column but not for the others.
I assume is because the values in Column A are text and in Column B+C are numeric.

I tried to twitch the Named ranges for columns B and C by changing the countif to ">=0" and other variations but nothing really worked properly.

I feel that I am close to the solution, but it seems that getting all 3 ranges working properly in the graph is the hard part.

Attached is the sheet with the 3 named ranges using your formula for all 3 columns

I added the data also to the graph so it reflects the named ranges.

Cells H2;J2 have the validation for all 3 named ranges.

If the ranges are all the same size, as in the example, you can just use the COUNTIF from the first dynamic range.

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTIF(Sheet1!$A:$A,"> "))
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTIF(Sheet1!$A:$A,"> "))
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTIF(Sheet1!$A:$A,"> "))

If they're different and won't have zero values, you could use:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTIF(Sheet1!$B:$B,">0")+1)
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTIF(Sheet1!$C:$C,">0")+1)

The +1 is to allow for the header row which isn't numeric and won't be picked up in the COUNTIF

Regards, TMS

The ranges are all the same size and using the countif from the first dynamic range works like a charm

YES!! Awesome

Thank you

You're welcome. Thanks for the rep.