software

I have a 450 record listing that has five columns in it, of which, column C
is quot;Locationquot;. I also have a named range in Column J called
quot;ClosedLocationsquot;. I want to filter my 450-record listing to exclude any
record that has a location that is found in the quot;ClosedLocationsquot; named
range.

Any suggestions?

Thanks in advance.
House
--
Dawg House Inc.
quot;We live in it, therefore, we know it!quot;

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:
gt; I have a 450 record listing that has five columns in it, of which, column C
gt; is quot;Locationquot;. I also have a named range in Column J called
gt; quot;ClosedLocationsquot;. I want to filter my 450-record listing to exclude any
gt; record that has a location that is found in the quot;ClosedLocationsquot; named
gt; range.
gt;
gt; Any suggestions?
gt;
gt; Thanks in advance.
gt; House

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 quot;Locationquot;
In Col J, I have a 5-record listing named quot;ClosedLocationsquot; (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 quot;then run Advanced Filter on the data areaquot;.

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.
quot;We live in it, therefore, we know it!quot;quot;Aladin Akyurekquot; wrote:

gt; Let A house the records from A4 on, with headers in A44.
gt;
gt; Create the criteria range: F2:F3 with
gt;
gt; F2 housing the text value XLoc
gt; F3:
gt;
gt; =ISNA(MATCH(C5,ClosedLocations,0))
gt;
gt; Then run Advanced Filter on the data area.
gt;
gt; Dawg House Inc wrote:
gt; gt; I have a 450 record listing that has five columns in it, of which, column C
gt; gt; is quot;Locationquot;. I also have a named range in Column J called
gt; gt; quot;ClosedLocationsquot;. I want to filter my 450-record listing to exclude any
gt; gt; record that has a location that is found in the quot;ClosedLocationsquot; named
gt; gt; range.
gt; gt;
gt; gt; Any suggestions?
gt; gt;
gt; gt; Thanks in advance.
gt; gt; House
gt;

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:
gt; Thanks for your help, but I'm not sure I follow it.
gt;
gt; I have A$1$:E$:450 filled with records.
gt; I have Col C labeled quot;Locationquot;
gt; In Col J, I have a 5-record listing named quot;ClosedLocationsquot; (J1:J5)
gt;
gt; I don't know if I understand what you're suggesting I do with the F2:F3 info
gt; nor do I follow the quot;then run Advanced Filter on the data areaquot;.
gt;
gt; The Adv Filter is looking for:
gt;
gt; List Range: A2:E451 (A1:E1 are row headers)
gt; Criteria Range: ???? Is this where you're suggesting I put the ISNA function?
gt;
gt; Still confused....
gt; House
gt;

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:

quot;The extract range has a missing or illegal field name.quot;

Suggestions?
--
Dawg House Inc.
quot;We live in it, therefore, we know it!quot;quot;Aladin Akyurekquot; wrote:

gt; 1. In F1 enter: XLoc
gt; 2. In F2 enter:
gt;
gt; =ISNA(MATCH(C2,ClosedLocations,0))
gt;
gt; 3. Select A1:E450.
gt; 4. Activate Data|Filter|Advanced Filter.
gt; 5. Check the option: Copy to another location.
gt; 6. Enter $A$1:$E$450 for List range.
gt; 7. Enter $F$1:$F$2 for Criteria range.
gt; 8. Enter $F$3 for Copy to.
gt; 9. Leave unchecked the option: Unique records only.
gt; Click OK.
gt;
gt; Dawg House Inc wrote:
gt; gt; Thanks for your help, but I'm not sure I follow it.
gt; gt;
gt; gt; I have A$1$:E$:450 filled with records.
gt; gt; I have Col C labeled quot;Locationquot;
gt; gt; In Col J, I have a 5-record listing named quot;ClosedLocationsquot; (J1:J5)
gt; gt;
gt; gt; I don't know if I understand what you're suggesting I do with the F2:F3 info
gt; gt; nor do I follow the quot;then run Advanced Filter on the data areaquot;.
gt; gt;
gt; gt; The Adv Filter is looking for:
gt; gt;
gt; gt; List Range: A2:E451 (A1:E1 are row headers)
gt; gt; Criteria Range: ???? Is this where you're suggesting I put the ISNA function?
gt; gt;
gt; gt; Still confused....
gt; gt; House
gt; gt;
gt;

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
quot;filteredquot; 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.
quot;We live in it, therefore, we know it!quot;quot;Dawg House Incquot; wrote:

gt; Thanks for the steps. I did them to the letter, but received an error on the
gt; second record (I'm assuming second, since it copied the first, then failed).
gt; The error received was:
gt;
gt; quot;The extract range has a missing or illegal field name.quot;
gt;
gt; Suggestions?
gt; --
gt; Dawg House Inc.
gt; quot;We live in it, therefore, we know it!quot;
gt;
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt; gt; 1. In F1 enter: XLoc
gt; gt; 2. In F2 enter:
gt; gt;
gt; gt; =ISNA(MATCH(C2,ClosedLocations,0))
gt; gt;
gt; gt; 3. Select A1:E450.
gt; gt; 4. Activate Data|Filter|Advanced Filter.
gt; gt; 5. Check the option: Copy to another location.
gt; gt; 6. Enter $A$1:$E$450 for List range.
gt; gt; 7. Enter $F$1:$F$2 for Criteria range.
gt; gt; 8. Enter $F$3 for Copy to.
gt; gt; 9. Leave unchecked the option: Unique records only.
gt; gt; Click OK.
gt; gt;
gt; gt; Dawg House Inc wrote:
gt; gt; gt; Thanks for your help, but I'm not sure I follow it.
gt; gt; gt;
gt; gt; gt; I have A$1$:E$:450 filled with records.
gt; gt; gt; I have Col C labeled quot;Locationquot;
gt; gt; gt; In Col J, I have a 5-record listing named quot;ClosedLocationsquot; (J1:J5)
gt; gt; gt;
gt; gt; gt; I don't know if I understand what you're suggesting I do with the F2:F3 info
gt; gt; gt; nor do I follow the quot;then run Advanced Filter on the data areaquot;.
gt; gt; gt;
gt; gt; gt; The Adv Filter is looking for:
gt; gt; gt;
gt; gt; gt; List Range: A2:E451 (A1:E1 are row headers)
gt; gt; gt; Criteria Range: ???? Is this where you're suggesting I put the ISNA function?
gt; gt; gt;
gt; gt; gt; Still confused....
gt; gt; gt; House
gt; gt; gt;
gt; gt;

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:
gt; My apologies...I had a reference issue in the originating cells. I fixed
gt; that, but its still not operating correctly. Some records appear to be
gt; quot;filteredquot; properly, but others who's location is definitely closed, are
gt; still coming across into the results and not being filtered. The second
gt; record was filtered, even though its location was not in the ClosedLocations
gt; list, while records 6 and 7 were both not filtered, even though their
gt; locations were the first location listed in the ClosedLocations range.
gt;
gt; Confused...but persistent.
gt; House

Dawg House Inc wrote:
gt; I have A$1$:E$450 filled with records.
gt; I have Col C labeled quot;Locationquot;
gt; In Col J, I have a 5-record listing named quot;ClosedLocationsquot; (J1:J5)
gt; ... I want to filter my 450-record listing to exclude any record
gt; that has a location that is found
gt; in the quot;ClosedLocationsquot; 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))),quot;quot;,
INDEX(X!A:A,MATCH(SMALL($G:$G,ROW(A1)),$G:$G,0)))
Copy A2 to E2

Put in G2:
=IF(X!C2=quot;quot;,quot;quot;,IF(SUMPRODUCT(--ISNUMBER(SEARCH(X!C2,ClosedLocations)))gt;0,quot;quot;,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
savefile.com/projects/236895
xdemechanik
---

Here's a sample construct to illustrate:
www.savefile.com/files/6286898
Auto-Exclude Filter based on a Named Range.xls
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---