If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
I have a 450 record listing that has five columns in it, of which, column C
is "Location". I also have a named range in Column J called "ClosedLocations". I want to filter my 450-record listing to exclude any record that has a location that is found in the "ClosedLocations" named range. Any suggestions? Thanks in advance. House -- Dawg House Inc. "We live in it, therefore, we know it!" |
#2
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
Let A house the records from A4 on, with headers in A44.
Create the criteria range: F2:F3 with F2 housing the text value XLoc F3: =ISNA(MATCH(C5,ClosedLocations,0)) Then run Advanced Filter on the data area. Dawg House Inc wrote: I have a 450 record listing that has five columns in it, of which, column C is "Location". I also have a named range in Column J called "ClosedLocations". I want to filter my 450-record listing to exclude any record that has a location that is found in the "ClosedLocations" named range. Any suggestions? Thanks in advance. House |
#3
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
Thanks for your help, but I'm not sure I follow it.
I have A$1$:E$:450 filled with records. I have Col C labeled "Location" In Col J, I have a 5-record listing named "ClosedLocations" (J1:J5) I don't know if I understand what you're suggesting I do with the F2:F3 info nor do I follow the "then run Advanced Filter on the data area". The Adv Filter is looking for: List Range: A2:E451 (A1:E1 are row headers) Criteria Range: ???? Is this where you're suggesting I put the ISNA function? Still confused.... House -- Dawg House Inc. "We live in it, therefore, we know it!" "Aladin Akyurek" wrote: Let A house the records from A4 on, with headers in A44. Create the criteria range: F2:F3 with F2 housing the text value XLoc F3: =ISNA(MATCH(C5,ClosedLocations,0)) Then run Advanced Filter on the data area. Dawg House Inc wrote: I have a 450 record listing that has five columns in it, of which, column C is "Location". I also have a named range in Column J called "ClosedLocations". I want to filter my 450-record listing to exclude any record that has a location that is found in the "ClosedLocations" named range. Any suggestions? Thanks in advance. House |
#4
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
1. In F1 enter: XLoc
2. In F2 enter: =ISNA(MATCH(C2,ClosedLocations,0)) 3. Select A1:E450. 4. Activate Data|Filter|Advanced Filter. 5. Check the option: Copy to another location. 6. Enter $A$1:$E$450 for List range. 7. Enter $F$1:$F$2 for Criteria range. 8. Enter $F$3 for Copy to. 9. Leave unchecked the option: Unique records only. Click OK. Dawg House Inc wrote: Thanks for your help, but I'm not sure I follow it. I have A$1$:E$:450 filled with records. I have Col C labeled "Location" In Col J, I have a 5-record listing named "ClosedLocations" (J1:J5) I don't know if I understand what you're suggesting I do with the F2:F3 info nor do I follow the "then run Advanced Filter on the data area". The Adv Filter is looking for: List Range: A2:E451 (A1:E1 are row headers) Criteria Range: ???? Is this where you're suggesting I put the ISNA function? Still confused.... House |
#5
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
Thanks for the steps. I did them to the letter, but received an error on the
second record (I'm assuming second, since it copied the first, then failed). The error received was: "The extract range has a missing or illegal field name." Suggestions? -- Dawg House Inc. "We live in it, therefore, we know it!" "Aladin Akyurek" wrote: 1. In F1 enter: XLoc 2. In F2 enter: =ISNA(MATCH(C2,ClosedLocations,0)) 3. Select A1:E450. 4. Activate Data|Filter|Advanced Filter. 5. Check the option: Copy to another location. 6. Enter $A$1:$E$450 for List range. 7. Enter $F$1:$F$2 for Criteria range. 8. Enter $F$3 for Copy to. 9. Leave unchecked the option: Unique records only. Click OK. Dawg House Inc wrote: Thanks for your help, but I'm not sure I follow it. I have A$1$:E$:450 filled with records. I have Col C labeled "Location" In Col J, I have a 5-record listing named "ClosedLocations" (J1:J5) I don't know if I understand what you're suggesting I do with the F2:F3 info nor do I follow the "then run Advanced Filter on the data area". The Adv Filter is looking for: List Range: A2:E451 (A1:E1 are row headers) Criteria Range: ???? Is this where you're suggesting I put the ISNA function? Still confused.... House |
#6
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
My apologies...I had a reference issue in the originating cells. I fixed
that, but its still not operating correctly. Some records appear to be "filtered" properly, but others who's location is definitely closed, are still coming across into the results and not being filtered. The second record was filtered, even though its location was not in the ClosedLocations list, while records 6 and 7 were both not filtered, even though their locations were the first location listed in the ClosedLocations range. Confused...but persistent. House -- Dawg House Inc. "We live in it, therefore, we know it!" "Dawg House Inc" wrote: Thanks for the steps. I did them to the letter, but received an error on the second record (I'm assuming second, since it copied the first, then failed). The error received was: "The extract range has a missing or illegal field name." Suggestions? -- Dawg House Inc. "We live in it, therefore, we know it!" "Aladin Akyurek" wrote: 1. In F1 enter: XLoc 2. In F2 enter: =ISNA(MATCH(C2,ClosedLocations,0)) 3. Select A1:E450. 4. Activate Data|Filter|Advanced Filter. 5. Check the option: Copy to another location. 6. Enter $A$1:$E$450 for List range. 7. Enter $F$1:$F$2 for Criteria range. 8. Enter $F$3 for Copy to. 9. Leave unchecked the option: Unique records only. Click OK. Dawg House Inc wrote: Thanks for your help, but I'm not sure I follow it. I have A$1$:E$:450 filled with records. I have Col C labeled "Location" In Col J, I have a 5-record listing named "ClosedLocations" (J1:J5) I don't know if I understand what you're suggesting I do with the F2:F3 info nor do I follow the "then run Advanced Filter on the data area". The Adv Filter is looking for: List Range: A2:E451 (A1:E1 are row headers) Criteria Range: ???? Is this where you're suggesting I put the ISNA function? Still confused.... House |
#7
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
It should work as advertised though... Perhaps you have a matching
problem, e.g., ff the Location entries are surrounded with extra spaces or other unvisble chars, the MATCH formula would fail. Dawg House Inc wrote: My apologies...I had a reference issue in the originating cells. I fixed that, but its still not operating correctly. Some records appear to be "filtered" properly, but others who's location is definitely closed, are still coming across into the results and not being filtered. The second record was filtered, even though its location was not in the ClosedLocations list, while records 6 and 7 were both not filtered, even though their locations were the first location listed in the ClosedLocations range. Confused...but persistent. House |
#8
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
Dawg House Inc wrote:
I have A$1$:E$450 filled with records. I have Col C labeled "Location" In Col J, I have a 5-record listing named "ClosedLocations" (J1:J5) ... I want to filter my 450-record listing to exclude any record that has a location that is found in the "ClosedLocations" named range. Perhaps another play to tinker with .. Assume source table above is in sheet: X In another sheet: Y With A1:E1 housing the same col headers as in X Put in A2: =IF(ISERROR(SMALL($G:$G,ROW(A1))),"", INDEX(X!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0))) Copy A2 to E2 Put in G2: =IF(X!C2="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(X!C2,ClosedLocations)))0,"",ROW() )) Select A2:G2, fill down to say, G500? (cover the max expected extent of source data in X) (Leave G1 empty) Y will auto-return the required results, i.e. only the lines from X whose locations are not amongst those listed in ClosedLocations, with all lines neatly bunched at the top -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
|
|||
|
|||
How Do I Do an Exclude Filter based on a Named Range?
Here's a sample construct to illustrate:
http://www.savefile.com/files/6286898 Auto-Exclude Filter based on a Named Range.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Tables - Named Range | dipsy | Worksheet Functions | 5 | August 23rd, 2005 04:50 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 04:44 PM |
Printing named range only | ray | Worksheet Functions | 4 | April 26th, 2004 05:29 PM |