Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Let A:D house the records from A4 on, with headers in A4:D4.
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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:D house the records from A4 on, with headers in A4:D4. 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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Range matching multiple named Ranges | Excel Discussion (Misc queries) | |||
Named Range Calculations | Excel Discussion (Misc queries) | |||
advanced filter a range | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Identifying single column within named range | Excel Discussion (Misc queries) |