I have a bunch of named ranges with this formula to change the range length when the data changes. Occasionally at the end of the data I will get a rows of #N/A if the data query does not pull any data for the entire data range.



How can I change this formula =OFFSET('Misc Calculations'!$T$9,0,0,COUNTA('Misc Calculations'!$T:$T)-1) to exclude the #N/A in the rows? When it includes it my graphs get all messed up because it includes this data on the graph.


Thank you,

Try :

To avoid the volatile OFFSET you can use the following as named range

=$t$9:index(T:T,match(9.99e307,T:T))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Logit, thank you for the information, this worked great.

Pecoflyer, I tired the code you suggested but received an error. I will keep trying.

I appreciate the input from both of you!!!

Thank you,

dan

You are welcome.

Just as a side note, I noticed that once I added the IFERROR statement the AGGREGATE function no longer worked.

dan.