Dynamic Range Values Only

[wsnyder]

Hi,

How can I create a Dynamic Named Range of values only?

For example the entire range is $I$4:$I$21. However, only some cells have values, others have #N/A which results from this formula:
=IFERROR(GETPIVOTDATA("Sales",$E$3,"App",$H10,NA()).

So based on the Slicer selection, a varying number of cells are returned with values.

Thanks,
w

 

[Dermot]

give your named range this formula
=FILTER($I$4:$I$21,IFNA($I$4:$I$21,FALSE))

 

[wsnyder]

Thanks,

Any options compatible with all versions of Excel?

Thanks,
w

 

[Dermot]

Not that I can think of