Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default How Do I Do an Exclude Filter based on a Named Range?

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
 
Posts: n/a
Default 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: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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dawg House Inc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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
---


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Range matching multiple named Ranges ben simpson Excel Discussion (Misc queries) 0 March 15th 06 07:45 PM
Named Range Calculations Peter Bernadyne Excel Discussion (Misc queries) 2 March 9th 06 04:39 PM
advanced filter a range Il Principe Excel Worksheet Functions 2 August 1st 05 03:27 PM
Offset Function works in cell, not in named range DragonslayerApps Excel Worksheet Functions 0 July 25th 05 04:39 PM
Identifying single column within named range ESAEO Excel Discussion (Misc queries) 2 March 24th 05 10:30 PM


All times are GMT +1. The time now is 01:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"