#1
|
|||
|
|||
Named Ranges Help
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, |
#2
|
|||
|
|||
Try :
Code:
=IFERROR(OFFSET('Misc Calculations'!$T$9,0,0,COUNTA('Misc Calculations'!$T:$T)-1),"") |
#3
|
||||
|
||||
To avoid the volatile OFFSET you can use the following as named range
=$t$9:index(T:T,match(9.99e307,T:T))
__________________
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
You are welcome.
|
#6
|
|||
|
|||
Just as a side note, I noticed that once I added the IFERROR statement the AGGREGATE function no longer worked.
dan. |
Tags |
excluding rows, named ranges |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Take String of numbers, expand ranges, sort, then compress back into ranges | AustinBrister | Word VBA | 19 | 08-22-2016 05:18 PM |
Data Validation List based on Two Named Ranges | Rich18144 | Excel | 4 | 01-14-2016 04:37 AM |
How to use named ranges in excel vba? | bosve73 | Excel Programming | 4 | 01-25-2012 09:26 AM |
Dynamic Named Ranges using text | hannu | Excel | 0 | 06-22-2010 04:42 PM |
Can't import home adresses in outlook 2010 from excel named ranges | eekie | Outlook | 0 | 05-14-2010 02:04 PM |