A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How Do I Do an Exclude Filter based on a Named Range?



 
 
Thread Tools Display Modes
  #1  
Old April 10th, 2006, 09:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 10th, 2006, 09:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old April 10th, 2006, 09:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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 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  
Old April 10th, 2006, 10:08 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 10th, 2006, 10:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 10th, 2006, 10:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 10th, 2006, 11:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 11th, 2006, 12:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 11th, 2006, 02:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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
---


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 03:34 PM.


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