Dynamic named range with blanks (but still has formula in the cell)

[Chookz]

Hey Guys,
I have a range that consists of array formulas. The array formula also has an IFERROR formula attached to it to return a blank if there is an error (eg. formula - {=IFERROR(INDEX(Sheet1! etc etc.). The formula returns text in the cells

I want to make this a dynamic named range to include the cells where the array formula has returned a value, but not the ones that are blank due to an error (error comes up when there is no data entered in my raw data source).

I am using this named range as series values in a chart.

My usual dynamic named range formula:
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A$1:$A$1000))
Doesn't work because the COUNTA still counts the blank cells that the IFERROR formula returns when there is no source raw data for the array formula. I want only the cells with text in them.

I was using the following:
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A$1:$A$1000)-COUNTBLANK(Sheet1!$A$1:$A$1000))
and having success, until one day the list was fully blank (no source raw data for that day so the array formula returned an error, which the IFERROR made "").

Then the next day when my range has text data in it again the OFFSET formula is screwed up and my charts are all over the place.

My question is, is there a better way of creating a dynamic named range (than my COUNTA - COUNTBLANK method) for text that has formulas returning blanks ("")?

And/Or Does anyone have a solution to get my named range to remain intact in my chart series values, even when the list is empty? I think this has to do with my COUNTA-COUNTBLANK not being the best.

Hopefully this makes sense, sorry it's kind of hard to explain.
Cheers

 

[Aladin Akyurek]

A2:A11 houses:

KAD
NAD
VAD
blank (i.e., "")
JAD
XAD
blank
blank

What must the dynamic range include?

 

[Andrew Poulsom]

You could try referring your name to:

=OFFSET(Sheet1!$A$1,,,MAX(1,(COUNTA(Sheet1!$A$1:$A$1000)-COUNTBLANK(Sheet1!$A$1:$A$1000))))