Dynamic Range to Exclude Blank Cells with Formulas

[Dokat]

I have below dynamic range formula to automatically change chart range. However i need formula to ignore blank cells with formula and exclude it from the range. Did anyone came acriss similar issue?

=(Inno!$AU$6:INDEX(Inno!$AU6:$AU$65,COUNTIF(Inno!$AU6:$AU$65,"<>"&""))

 

[Jeffrey Mahoney]

Does your column of data have blank cells throughout the column? Are you asking for the chart data not to use the blanks within the range?
If so, I don't think your going to get what you want. A chart series has to be contiguous (continuous, uninterrupted set of data) range. If you need your non-blank cells aggregated together, we would have to go a different way.

Dynamic named ranges count the number of non-blank cells and return a row. You can have it return the last non-blank row. In your case, Countif is only counting the non-blank cells. It won't include all the data if blanks are included.

=offset($AU$6,0,0,match(1e+300,$AU$6:$AU$65),1)
This would return the last row with a number in it. It would still return the full range including any blanks.

 

[Dokat]

Does your column of data have blank cells throughout the column? Are you asking for the chart data not to use the blanks within the range?
If so, I don't think your going to get what you want. A chart series has to be contiguous (continuous, uninterrupted set of data) range. If you need your non-blank cells aggregated together, we would have to go a different way.

Dynamic named ranges count the number of non-blank cells and return a row. You can have it return the last non-blank row. In your case, Countif is only counting the non-blank cells. It won't include all the data if blanks are included.

=offset($AU$6,0,0,match(1e+300,$AU$6:$AU$65),1)
This would return the last row with a number in it. It would still return the full range including any blanks.

Thank you