Criteria for Advanced Filter HELP

I have what I think is a simple question that I need answered IMMEDIATELY.

I have a spreadsheet with 20 fields and I want to filter it so it only shows 
records that have something in of any of 5 of those fields.  So once 
filtered, I will not have any records that have all 5 of those fields blank.

I know I have done this before but I'll be damned if I can remember or find 
out how.

-- 
Thanks

You all are teaching me so much
0
2/20/2009 6:44:01 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
934 Views

Similar Articles

[PageSpeed] 12

don't cross-post -- answer supplied in misc NG

"knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote in 
message news:ACBC0982-86A5-4E54-8839-4B9413AC4F5D@microsoft.com...
>I have what I think is a simple question that I need answered IMMEDIATELY.
>
> I have a spreadsheet with 20 fields and I want to filter it so it only 
> shows
> records that have something in of any of 5 of those fields.  So once
> filtered, I will not have any records that have all 5 of those fields 
> blank.
>
> I know I have done this before but I'll be damned if I can remember or 
> find
> out how.
>
> -- 
> Thanks
>
> You all are teaching me so much 


0
rumlas (268)
2/20/2009 6:58:08 PM
I don't mean to sound flip.  I really don't but I don't understand your 
response in "NG."  And I honestly don't even know how to articulate what I 
don't understand about it.  I was hoping someone else would answer that would 
give more detail.

I am trying to do an advanced filter.

My list range is $A$15:$AJ$875
The cells where I have my criteria range is $AB$1:$AJ$10

I have tried putting ="<>""" in AB2, AC3, AD4, AE5, AF6, AG7, AH8, AI9, and 
AJ10.  It seems that it just coies the whole list range to the destination.  
I still have lots of records that have blanks in fields AB to AJ. 

-- 
Thanks

You all are teaching me so much


"Bob Umlas" wrote:

> don't cross-post -- answer supplied in misc NG
> 
> "knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote in 
> message news:ACBC0982-86A5-4E54-8839-4B9413AC4F5D@microsoft.com...
> >I have what I think is a simple question that I need answered IMMEDIATELY.
> >
> > I have a spreadsheet with 20 fields and I want to filter it so it only 
> > shows
> > records that have something in of any of 5 of those fields.  So once
> > filtered, I will not have any records that have all 5 of those fields 
> > blank.
> >
> > I know I have done this before but I'll be damned if I can remember or 
> > find
> > out how.
> >
> > -- 
> > Thanks
> >
> > You all are teaching me so much 
> 
> 
> 
0
2/20/2009 8:00:08 PM
NG = NewsGroup
I gave the answer in Microsoft.public.excel.misc, where you posted it as 
well as here (microsoft.public.excel.newusers), meaning you posted it twice, 
and that's not the "proper" etiquette, only because you may get 2 or more 
people spending time answering your question only to find it's already been 
answered.
My answer in the other section was:

Something like this in Z2, where Z1:Z2 is the criteria:
=OR(B2<>"",F2<>"",G2<>"",H2<>"",K2<>"")

"knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote in 
message news:D78568BC-AE5B-4CE4-93FF-DD9009DBEF70@microsoft.com...
>I don't mean to sound flip.  I really don't but I don't understand your
> response in "NG."  And I honestly don't even know how to articulate what I
> don't understand about it.  I was hoping someone else would answer that 
> would
> give more detail.
>
> I am trying to do an advanced filter.
>
> My list range is $A$15:$AJ$875
> The cells where I have my criteria range is $AB$1:$AJ$10
>
> I have tried putting ="<>""" in AB2, AC3, AD4, AE5, AF6, AG7, AH8, AI9, 
> and
> AJ10.  It seems that it just coies the whole list range to the 
> destination.
> I still have lots of records that have blanks in fields AB to AJ.
>
> -- 
> Thanks
>
> You all are teaching me so much
>
>
> "Bob Umlas" wrote:
>
>> don't cross-post -- answer supplied in misc NG
>>
>> "knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote 
>> in
>> message news:ACBC0982-86A5-4E54-8839-4B9413AC4F5D@microsoft.com...
>> >I have what I think is a simple question that I need answered 
>> >IMMEDIATELY.
>> >
>> > I have a spreadsheet with 20 fields and I want to filter it so it only
>> > shows
>> > records that have something in of any of 5 of those fields.  So once
>> > filtered, I will not have any records that have all 5 of those fields
>> > blank.
>> >
>> > I know I have done this before but I'll be damned if I can remember or
>> > find
>> > out how.
>> >
>> > -- 
>> > Thanks
>> >
>> > You all are teaching me so much
>>
>>
>> 


0
rumlas (268)
2/20/2009 8:53:11 PM
I am saying that I read your answer and I don't understand it.
-- 
Thanks

You all are teaching me so much


"Bob Umlas" wrote:

> NG = NewsGroup
> I gave the answer in Microsoft.public.excel.misc, where you posted it as 
> well as here (microsoft.public.excel.newusers), meaning you posted it twice, 
> and that's not the "proper" etiquette, only because you may get 2 or more 
> people spending time answering your question only to find it's already been 
> answered.
> My answer in the other section was:
> 
> Something like this in Z2, where Z1:Z2 is the criteria:
> =OR(B2<>"",F2<>"",G2<>"",H2<>"",K2<>"")
> 
> "knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote in 
> message news:D78568BC-AE5B-4CE4-93FF-DD9009DBEF70@microsoft.com...
> >I don't mean to sound flip.  I really don't but I don't understand your
> > response in "NG."  And I honestly don't even know how to articulate what I
> > don't understand about it.  I was hoping someone else would answer that 
> > would
> > give more detail.
> >
> > I am trying to do an advanced filter.
> >
> > My list range is $A$15:$AJ$875
> > The cells where I have my criteria range is $AB$1:$AJ$10
> >
> > I have tried putting ="<>""" in AB2, AC3, AD4, AE5, AF6, AG7, AH8, AI9, 
> > and
> > AJ10.  It seems that it just coies the whole list range to the 
> > destination.
> > I still have lots of records that have blanks in fields AB to AJ.
> >
> > -- 
> > Thanks
> >
> > You all are teaching me so much
> >
> >
> > "Bob Umlas" wrote:
> >
> >> don't cross-post -- answer supplied in misc NG
> >>
> >> "knowshowrosegrows" <knowshowrosegrows@discussions.microsoft.com> wrote 
> >> in
> >> message news:ACBC0982-86A5-4E54-8839-4B9413AC4F5D@microsoft.com...
> >> >I have what I think is a simple question that I need answered 
> >> >IMMEDIATELY.
> >> >
> >> > I have a spreadsheet with 20 fields and I want to filter it so it only
> >> > shows
> >> > records that have something in of any of 5 of those fields.  So once
> >> > filtered, I will not have any records that have all 5 of those fields
> >> > blank.
> >> >
> >> > I know I have done this before but I'll be damned if I can remember or
> >> > find
> >> > out how.
> >> >
> >> > -- 
> >> > Thanks
> >> >
> >> > You all are teaching me so much
> >>
> >>
> >> 
> 
> 
> 
0
2/20/2009 9:16:03 PM
Maybe simpler just to use autofilter on a helper col

Assume your 20 cols in cols A to T, data from row2 down
In say, U2: =COUNTA(A2:T2)>=5
Copy U2 down. Then apply autofilter on col U, choose: TRUE

-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
0
demechanik (4694)
2/21/2009 12:32:01 PM
Reply:

Similar Artilces:

help needed error in com dll
i get the following error when i compile my comdll can anybody help. C:\Program Files\Microsoft Visual Studio\VC98\ATL\INCLUDE\atlcom.h(1827) : error C2259: 'CComObject<class CGSMModm>' : cannot instantiate abstract class due to following members: C:\Program Files\Microsoft Visual Studio\VC98\ATL\INCLUDE\atlcom.h(1823) : while compiling class-template member function 'long __stdcall ATL::CComCreator<class ATL::CComObject<class CGSMModm> >::CreateInstance(void *,const struct _GUID &,vo id ** )' ...

Outlook 2002 junk senders vs junk filter
Is there a way to have just the junk senders list enabled without the default junk filters enabled? It seems that you cannot modify the junk filters. Is my only option to manual create a rule for the junk senders? Lee <Lee@discussions.microsoft.com> wrote: > Is there a way to have just the junk senders list enabled without the > default junk filters enabled? It seems that you cannot modify the > junk filters. Is my only option to manual create a rule for the junk > senders? Since the OL 2002 junk filter is nearly worthless anyway, having it enabled is just about lik...

computing formula according to criteria #3
Thanks, That's a good idea, the problem is I have several participants, and need a template sheet where I can just paste each participant's data and the means and SD's will be computed automatically. At the momen going over each and every participant is taking me hours. I really need a formula... -- lior ----------------------------------------------------------------------- liory's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1049 View this thread: http://www.excelforum.com/showthread.php?threadid=26138 another way to do it would be to write a ...

Please help with date insertion
Not very good with excel so hope you can help I want to have a table with the date in column A a figure in column B wihich calculates a figure in colomn C Have worked out the formula for column B & C and a graph drawn for these figures. What I want to know is 2 things 1. how can I automatically have the date inserted in Column A when I enter this No? 2. How can I have a graph that only takes the sample data from colummn B against C for the last 7 days, constantly updating itself evry day that a new figure is entered Hope I have made myself clear dates from say 1st-31st of each month in co...

Multiple criteria #3
I was looking for help with the following code. I would like to count the agents hired after 2002 and were hired directly Dim hiretype As Text Dim agntct As Integer Dim dbs As Database, rst As Recordset hiretype = "Direct" agntct = DCount("[Agent Hire Date]", "tblAgentdetail", "[Hire Type]= '" & hiretype & "' and [Hire Date]> #12/31/2002# ") ' Return reference to current database.D Set dbs = CurrentDb ' Open table-type Recordset object. Set rst = dbs.OpenRecordset("TestAG") rst.AddNe...

Formula Help #48
Hi, I have a excel spreadsheet and on the first column I have my customer and on the second column I have TIER 1, 2, 3, 4, and I have their Vali From and Valid To date on. Because of the system I am using all m dates are not flowing. Is there a formula which would tell me tha this customer�s date is not flowing and I could just go back in ther and fix it. If someone knows can you please please help me. I woul be more than happy to send out the visual. Thank you +------------------------------------------------------------------- |Filename: example.doc ...

I need help.
Sorry, I'm new to this, I just bought microsoft office home and student 2007 at Radio Shack (Brand new), inserted the CD in my Compaq deskpro EN (Runs Windows XP), typed in the correct product code (Got a green check mark) Then after about 10-15 seconds the error message "microsoft office home and student 2007 encountered an error during setup", Ive tried everything, can someone help me please? -- -V Hi Vance, Personally, I would check if the CD is scratched and take it back to the supplier. It should not give you that error at all. Regards Garreth --...

Please help
Hi Folks, I can't figure out why the Budget feature changes the amounts I budget for certain items. I have a recurring bill entered in the bills and deposits. It shows up correctly when I edit my budget. There are no duplicate entries for this category. Yet when I look at my monthly budget, it sometimes doubles or otherwise changes the values I have budgeted for that bill. As I understand it, the autobudget feature is causing all the problems. But, I have made every effort to avoid using the autobudget. I have also changed all transactions in my checking account for that bill to a co...

OLAP filters from cells
Hi, not sure if my title makes sense, but I have a spreadsheet that has multiple pivot tables accessing the same cube. I want to apply the same filter to all pivot tables, preferably from a cell on a different worksheet. I am realitvely inexperienced with Excel, but do not seem to be able to find a reference to this functionality on the web. It may be that I am asking the wrong questions. The version of Excel is 2007 and I am connecting to SQL 2008 Analysis services. Any help would be much appreciated. thanks Jason Will all of the pivot have the same filter built in a...

DMAX with Criteria
Is there a way to add criteria to the expression that creates my serial numbers? Me![TxID] = Format(DMax("[TxID]", "[tblMyTable]") + 1, "0000") I want the next number in the series to be based on a category field on the form. which will be matched to a ctegory field in MyTable. In other words: The user allocates the record to a Category and clicks a button to return the next available number in the series for that category. This will result in duplicate numbers in the Seriel Number Field, but this is not my Primary Key, (I am using an Autonumbe...

AND on 2 fields along with other criteria in query
Hello New to Access and trying to figure out a problem with an AND test I have a database with 4 fields called A B C D Field A Must =1 (no problem with this) Field D Must > 1 (Again no problem) Fields B & C can be any number including zero, as long as BOTH fields are not zero. If I search using the criteria for fields A & D only, I find 390 records. If I search with criteria that says show me if both are zero, (B=0, C=0 on the criteria line, along with the other criteria) it shows me the 4 or so suspect records that have a zero in BOTH fields. If I use the opposite, and...

Create a list with multiple criteria
I have a data base with 1,000's of addresses w/zipcodes (Sheet 1 columns A to E, E being zipcodes). After setting a base address I get all of the zipcodes within a given radius (in this case 70 zipcodes), listed in Sheet 2, Column A. Next, I use COUNTIF (in column B) to find how many addresses are in each zipcode, in this example there are 46 addresses within the 70 zipcodes. What I want to do is create a list in sheet three that will list each with the data from Sheet 1 columns A to E). Any help would be appreciated. Ronbo ...

Publishing to website HELP ME PLEASE!!!!
I dont know if this is a publisher error or an error with the stupid host that I have!!! When I try to publish to the web, I get an error saying Publisher cannot publish to this location, please check URL or network connection. Now I know that there is no problem with the URL or Network connection, and sometimes the site starts to load and then stops. Any ideas what's wrong? ...

IIF function for 3 criteria, possible?
Hello, I am trying to do a query where I need a result based on 3 criteria, but not sure how to do it. in written term would be like this for a new fields (column in query). 1) If OprStat = 1 and StartDate < today date, return "LateN" 2) If OprStat = 3 and StartDate < today date, return "LateM" 3) If does not fall into (1) & (2) condition, return "OnTime" Sample Data: Order OprStat StartDate 1110 1 4/1/2010 1111 1 4/12/2010 1112 3 4/2/2010 Results: assuming current...

help needed
I have an excel spreadsheet which has a column of text in it. What I want to know, is it possible to search each row for a specific string and then extract the next 10 characters and copy it into a new colum?? Any help would be appreciated. thanks Is this a programming question (you have posted to several groups). Do you want to search in a single column or do you want to search the entire row. How about selecting all cells of interest and doing Edit=>Find which whole unchecked. If you turn on the macro recorder while you do this manually, this will provide you the code you need ...

Search folder criteria
I am using Outlook 2007. How can I customise my search folder to filter messages that contain "KSC" in subject OR belong to "Sports" category? Is there a reason why "KSC" cannot not be assigned "Sports" catagory? Melissa wrote: > I am using Outlook 2007. > How can I customise my search folder to filter messages that contain "KSC" > in subject OR belong to "Sports" category? ...

Advanced relaying / routing
I need to route outbound messages to specific relays based on sender. I also need to route all inbound messages to a specific domain to a specific smart host. Exchange is VERY rudimentary when needing to perform specific routing like this. What products or methods are out there to fulfill requests like these? Thanks! Perhaps you could share more about why you need to do these things. With more info the list might be able to offer suggestions. The first place for you to start looking is at SMTP Connectors. http://support.microsoft.com/kb/265293/en-us http://support.microsoft.com/kb/294...

Connecting Text Box in Query Criteria
I have created a Select query for retrieving some particular Date Records from a table using the BETWEEN expression in query criteria bymentioning the From Date and To Date and it’s working fine. Each time I don’t want to open the query and enter the From Date and To Date, so I want to create TWO Unbound text box in Forms for entering the From Date and To Date and nearby the text box I am having a command button to run the query. Now I want to know how I can connect the Two Unbound Text Boxes that is From Date and TO_DATE which is created in Forms in the Select Query (BETW...

help plz
I have a column with numbers, I have to generate a formula usinfg a if statement, that if its divisible by 3 than it should answer true, otherwise false =MOD(A1,3)=0 -- Kind regards, Niek Otten "zomex" <zomex@discussions.microsoft.com> wrote in message news:E4C5208C-ED50-47B4-A1A5-94B2ED525F74@microsoft.com... >I have a column with numbers, I have to generate a formula usinfg a if > statement, that if its divisible by 3 than it should answer true, > otherwise > false ...

Remove filter in worksheets before running code
How do I include all sheets in a workbook when running the following: If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If Thanks Hi, Sub Show_All() For x = 1 To Worksheets.Count If Sheets(x).FilterMode Then Sheets(x).ShowAllData End If Next End Sub Mike "gootroots" wrote: > How do I include all sheets in a workbook when running the following: > > If ActiveSheet.FilterMode Then > ActiveSheet.ShowAllData > End If > > Thanks Hi Mike It's stalling on X when put into the ...

Still puzzled with iif statement in spite of help received
Hi, I just got help on iif statement to handle null values. However I am applying the concept from prototype to real system. I am using the following to display Y if true(-1) and N (if false i.e. 0). =IIf([AlloydRecommendedGuage] Is Null,Null,IIf([AlloydRecommendedGuage],"Y","N")) However, now most of the values should be N (as these are 0) instead I am getting Y in all the rows for the above calculated field. This field in backend connected sql server is int instead of smallint. I am not sure if this datatype change has to do with the display though apparently it ...

[HELP] Windows Event log File read???
Hello, I am having a bit a problem in getting information out of the windows system event log file. I know how to get information out of the backup file of the event file. //------------------------------------------------------ OpenBackupEventLog Opens a handle to a backup event log. OpenEventLog Opens a handle to an event log. ------------------------------------------------------// But I do not know how to get informaiton out of the original event file. (C:\WINNT\system32\config\SysEvent.Evt C:\WINNT\system32\config\SecEvent.Evt C:\WINNT\system32\config\AppEvent.Evt) I ha...

Add some filter to the forum
Hi, The forum is really great, a lot of people do help and I got a lot of problems solved here! Thank you! but I very often have troubles to find a solution a second time, when I didn't make notes for it. A date filter and perhaps some other fields would really help I think ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreade...

Calc staff vacation time based on more than 1 criteria
I would like to calculate on a daily basis, accumulated vacation time based on the following criteria: Total daily hours (work and leave taken) Hours must fall on a week day Entitlement hours from another spreadsheet that are based on employee seniority and regular shift hours. 'Sheet1' in my 'Book1.xls' looks like this: A2:A372 Day of the week (starting with Sunday in A2) B2:B372 Date beginning with April 1, 2010 C2:C372 Explanation (if necessary) D2:D372 Work day 1 = it's a workday, blank = not a workday E2:E372 Paid Daily Total F2:F372 Worked hours ...

Auto filter on blank lines
I have auto filter turned on and when I filter on something there are no longer any blank lines at the bottom of the document (it's grey). I'm not sure what I did to get rid of them but I'd like to have them back. Thanks! On Jan 28, 10:24=A0am, Allana McDonald <Allana McDon...@discussions.microsoft.com> wrote: > I have auto filter turned on and when I filter on something there are no > longer any blank lines at the bottom of the document (it's grey). I'm not > sure what I did to get rid of them but I'd like to have them back. > > Tha...