Free Microsoft Excel 2013 Quick Reference

advanced filter or if functions

Good afternoon, I m trying to run a advanced filter on my datasheet so that the answers go to second sheet, this I have sorted out, my problem is that in the filter I used a formula > cell in my assumption table. If I change my assumption table I want excel to filter the new assumption automatically,

If this is not possible is it possible to do if function where if value is true to send the whole row information to the second sheet and if its false dont send anything to sheet 2. There must not be any rows open in sheet 2.

Thank you

Post your answer or comment

comments powered by Disqus

I would appreciate any help I can get at this point. I believe that what I need to do is a nested =If Function in order to do what I want but I have never done that. I am also sure there are other ways to accomplish my goal but Ill let you be the experts.


I am trying to return a list that has unsorted rows (this list is being compiled by use of a formula)

3 1 1 2

2 1 1 3

0 0 0 0

1 1 1 1

3 1 1 2

2 1 1 3

0 0 0 0

In a new sheet I would like to return all rows that do not contain 0 in the first column.

I would like this to be done through the use of a formula since sorting by the first row would unsort the other columns (may not make sense with my example but take my word for it.) So far I have

The Problem is each time this file is used the
3 1 1 2

2 1 1 3

0 0 0 0

Will be different each time with different amount of rows containing 0 0 0 0.

This may make no sense at all and if it does not... sorry but short of attaching the 20mb file Im lost for ideas.

I appreciate any help.



I have a list that is sorted based on component cost that combine to form a total cost and I would like to filter out the worst 10% (keep the top 90%) of the list. This way I can filter out 10% of the worst component costs which would lower my total cost by more than 10%. Does anyone know how to do this using an advanced filter (or if all else fails using the autofilter). Thanks in advance!!

Have $ amt data on F2:F31 need sort by =>$50K (equal to or greater than $50K)

help advanced filter or conditional formatting for excel 2003

Love this forum...

We have a process in place at our company that uses a print out where each title gets checked off by ops when a title has been loaded and meta data check and then returned to the programming department. This process is showing its age as we now tend to load so many asset that no real time data is shared (loading process and take a week). We want to make the paper form electronic and share it on a shared dropbox or something... Since we can export records from the report to excel I was hoping to use excel as the solution.

I"m trying to find a solution that will display records that meet a simple criteria. I see two possible answers but not sure which is best as they both are presenting me (excel semi novice) challenges. I'm using a mac w/ excel 2008 which does not support vba.

Criteria is if column a (asset loaded) and b (metadata checked) are yes then please display record (row) in results.

1. Advance filter. In the attached workbook you can see that I did a simple advance filter on the 'attempt advance filter' tab. It works but when you change the criteria nothing happens unless you run the function again (data menu / advance filter) which is clumsy. What am I missing???

2. Pivot table. I have read on your forum (and links I found on forum) that your very limited w/ pivot table that use consolidated data. I found this to be true. I created a simple pivot table from just one worksheet which represents one hotel which works great but I can not achieve the same data results from the consolidated one. I guess I could create a separate pivot table for each hotel and place in individual worksheets but I thought I would ask if there might be a better / clearer direction to go in.

Thanks on and all!

Hi All-

I'm a relative n00b so please go easy.

In Excel 2010, I have a spreadsheet with two tabs: the first tab contains two columns: A which lists the names of individuals and B which lists whether or not they are available (a simple "Yes" or "No" data validated list). So something basic like:

Bob Yes
Pete No
Jane No
Sue Yes

The second tab has a single column, C, against which I want to apply data validation to but only show those people that are available. In other words, I want to filter out those from the first tab's column A whose associated column B is "No." So, C's dropdown options using the sample set about would be "Bob" and "Sue" only.

Is there a simple, eloquent, way to do this WITHOUT using VBA, advanced filtering, etc. even if I have to create a working data set elsewhere? It seems like I should be able to use a named range and filter it somehow in a formula applied to the data validated list, but I can't figure it out nor find any similar solution online.

Thank you in advance!

Have spent a good while researching this and haven't found anything.

All I "simply" want to do is use auto filter and advanced filter to filter a column of numbers based on the first digits.

For example, let's say I have a column of data such as:-


I want to enter criteria such that I select all the rows that begin with "177".

You might have thought this was easy and just need to use 177* with the "begin with" auto filter statement or "=177*" with advanced filter. However, both criteria select nothing.

After a little testing I realised this is because excel must store the numbers as numbers (i.e. not strings). So if I insert a ' in all columns the above criteria works but NOT if I use the criteria as above.
The *silly* thing is if I try to match the whole number (not just first 3 digits") the criteria works fine.

Now, surely I'm missing something simple here and there is a way to filter part of a number?


I have a problem with "Advanced filter" function in Excel. I use VBA code to create a Advanced filter type of function that copies data from "Transactions" sheet to "Buy" and "Sell" sheets based on criteria defined in rows "A1:L2" in both " Buys" and "Sells" worksheets.

However when I use the advanced filter function what I get is all data from Companies that start with "REL" (REL, RELCAPITAL,RELGOLD,RELIANCE, RELIANCE PETROLEUM ETC ) instead of data from company "REL" alone. I think this is an Excel problem. Is there a way to overcome this thru programming?
I am attaching the "FIFO_test.xls" file for reference.

The program code is as below

     'Create List of Buys
    rngTransactions.AdvancedFilter Action:=xlFilterCopy, _ 
    CriteriaRange:=rngBuyCriteria, _ 
    CopyToRange:=RngBuyDestination, _ 
    Set rngBuys = Range(RngBuyDestination, RngBuyDestination.End(xlDown).End(xlToRight)) 
    rngBuys.Sort Key1:=RngBuyDestination(1, 1), Order1:=xlAscending, _ 
    Key2:=RngBuyDestination(1, 2), Order2:=xlAscending, _ 
    Header:=xlGuess, OrderCustom:=1, _ 
    MatchCase:=False, _ 
    Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal 
     'Copy Units to Units Remaining Column
    rngBuys.Columns(rngBuys.Columns.Count).Offset(0, 2).PasteSpecial xlPasteFormulasAndNumberFormats 
    Set rngUnitsRemaining = Range(rngBuys(1, rngBuys.Columns.Count).Offset(0, 2), _ 
    rngBuys(rngBuys.Rows.Count, rngBuys.Columns.Count).Offset(0, 2)) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Similar code for Sell has been implemented

Ramesh J

I need a solution of a problem regarding certain criteria for grading the students.
87 or above marks (A)
72-86 marks(B)
60-71 marks (C)
59 or below marks(F)
I have used Vlookup by writing 0-100 in ColA and Grading in ColB. But i want to concise it instead of giving 101 value, specially using IF function.
Thank You

I've got a VB script that needs to turn off an Advanced Filter or "Show All". The problem is, the Advanced Filter is not always turned on. So, I need a VB script that will turn off the Advanced Filter if it is already on and the script knows to do nothing if the Advanced Filter is already turned off.

Following is my initial thought to solve this problem.

If AdvancedFilter = True Then
End If

Unfortunately, this does not work. The code does not turn off the Advanced Filter.

My second attempt is as follows:

If AdvancedFilter = Active Then
End If

This code does turn off the Advanced Filter; however, I get a Run-time error if the Advanced Filter is already turned off. There is probably a combination of these two codes that will work, but I'm not getting it.

Any thoughts or suggestions are apprecaited.


I am trying to create a colour system based on a complicated 'Red, Amber, Green system' based on the number of 'red or green people'.

The system works like this;

Overall Red= >10% red people and <75% green people
Overall Amber= Either >10% red people and >75% green people,
or <10% red people and <75% green people
Overall Green= <10% red people and >75% green people

I am using three rows of cells for this, i.e.
Enter Red People=
Enter Green People=
Total Score

I would like the total score cell to turn Red, Amber or Green colour using the 'IF' Function or conditional formatting. However, as amber criteria as a 'OR' I cannot work out how to do this.
Can you help?

I am looking for some help with a project for work. I am not a very advanced Excel user and have not done much with Advanced Filtering or Macros...

Goal: Use command buttons to filter and "un-filter" a table using multiple criteria

I am completely open to other ideas if there is an easier approach.

Attached is a workbook with what I have put together so far.

I have a meeting this Friday 5/7 that I would like to have this ready for if possible - Any help would be greatly appreciated!

Thanks in advance -

Ok here goes. I am fairly decent with VBA, and can understand most code after looking at it for awhile even if I am unable to come up with on my own, but having spent sometime look for a solution I am struggling to find what I need .I have a large worksheet that has a couple of unique Identifiers in it. I am trying to do a advance filter or the equivalent to populate another sheet in the same workbook. On Sheet1 column A is a material number that is unique, Column L is also unique. What I am trying to accomplish is look at column A find the unique numbers copy them and paste them on Sheet2 in column A, but also look at Column L and add another row on Sheet2 with the same even if the Unique Material number is the same but Column L is Different. I am attaching a example work book of what I am trying to do.
Code I am using to Get the Unique Material Number in Column A
Sub extract()
With Sheet1
        .Range(Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1,
256), Unique:=True
End With
End Sub
But I am at a loss as to how to combine the two Unique Columns in a search.

In the Attached workbook the 1st 2 sheets Held & Data are what I currently have come up with. Sheet3 "Desired" is what I want it to look like. I appreciate everyone's help and assistance. I hope that I with my description of my issue and the attached workbook people will understand what I am trying to accomplish.


I have two worksheets on one I am inputing data and on the next one the same data appears using the equals or if function.

Is it possible to delete the data from the first worksheet and leave it on the second worksheet by using some other function?

I am having an issue in that I am using various formulas to move, split data, etc from various sources.
The problem is when my final results post to the final destination that I want, I still need to either run advanced filters, or a vlookup with the results. I can’t do this because as an example
if cell A1 shows a value of: A127
the actual cell content is: =RIGHT(A2,FIND(" ",A2&" ")-2)

Everything I read said to copy and paste special values, but this doesn’t work for me as the idea is to have the formulas/macros run everything and eliminating cutting and pasting.
In the case above I have a formula that pulls that info from a spreadsheet that is saved every week. Once it is pulled part of it is cut out in another column. I then need to run a vlookup on those results for data already contained on another tab.

I need to check two cells for blanks and report a zero if the cell is blank. If EITHER of the cells are not blank, I need to make a simple computation. I can write the formula for one cell or for the other, but I don't know how to COMBINE the two "IF" statements. In Lotus, I seem to recall a function called "OR_IF", but can't seem to find a way to do it in Excel. Here's the two formulae I need to combine:

=if(a1="",0,c1-d1) OR =if(b1="",0,c1-d1)

If EITHER a1 or b1 is blank, i want to subtract d1 from c1. Can anyone help me with the "OR" function with two embedded "IF" statements?


I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year. Thank you for any help.

Hi Everyone,

I'm new here and this place looks like a great forum to discuss ideas. I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters. Any help would be greatly appreciated.



This should be really easy, but I'm not sure why it's not working. I have several sheets in my WB, and need to hide all rows in Sheet 1 where the data in the ID column doesn't match ID data in Sheet 2.

For example:

Sheet 1:

ID | Type
1 | dog
2 | cat
3 | linux

Sheet 2:

ID | Type
1 | $4.95
3 | free

... I need to filter out all results from Sheet 1 where the ID is not contained on sheet 2. I had tried an advanced filter, but for some reason nothing happens when I apply the advanced filter? My list range was Sheet 1 A2:A106 and my Sheet two criteria was similar, but shorter (A2:A50).

Any ideas?


6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of
stocks (eg. I1:I100) in columns D-F and return only the stocks on my
list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done
with advanced filter or is there an easier way?

Potatosalad2's Profile:
View this thread:

I have several sheets using advanced filter to display data :
- automatically when you enter the sheet
- or upon change of any input criteria

I want to protect the sheet(s), have already unlocked the appropriate cells
(criteria -range only).
Then Tools - protection - sheet protection - allow autofiler

But the filter will no longer run... Does the checkbox (autofilter) in
Protection dialog box not apply to advanced filter, or am I doing something
wrong ?

I suspect I have to switch protection off and on before and after every
filteraction in the code...?

Any advise will be much appreciated

Greetings folks!
I can;t tell you enough how this forum has helped me out! I have a problem and I am stuck. I want to calculate flight hours based on a semi-annual period. I want my spreadsheet to determine the number of hours flown for that period, and when the period is over, (determined by the number of days counting down to zero), I want it to automatically start reading from the new period. I have included an example, but what I have looks like this. Here is the code to read the first semiannual period:

PHP Code:

	PHP Code:

	PHP Code:

OK - I have about 15 different cells which contain numerical values. If any of the cells are outside of the ranges I have set up with validation, then I would like a text phrase to appear at the bottom of the document.

I can use the "if" function to program the text phrase to appear, but it only works with up to seven of the cells. Someone indicated that the lookup function may work, but I can't seem to get it to.

Any ideas? See below for more info.

Value Range
1 0-1
2 0-2
3 0-1
4 0-8
5 0-2
6 0-2
7 0-1
8 0-9
9 0-3
10 0-7

In this example, since # 3 is outside of the spec. range, I would like to enter the text "Out of Range" at the bottom of the worksheet. The "out of range" needs to be entered if ANY of the values are outside of the spec. range.

Any help is GREATLY appreciated.


I want to create a rule or an IF function to give me a percentage based on a range of possible totals. Example:

If value is under 100, 15% will be charged.
If value is between 101-200, 10% will be charged.
If value is 201 or greater, 5% will be charged.

Say that my values are in column C and I want the corresponding percentage to be generated using a rule or IF function to be displayed in column A. Is this possible? If so, how would write the function?

(I only need the number to appear) ex: If my value is 120 in column C, I want the function to insert 10% in column A.

Sorry if this not worded or explained clearly, but thank you for the help.

6/6/2005 12:30 DBAB Media VIA.B
6/6/2005 14:00 DBAB Media SFA YBTVA
6/6/2005 14:30 DBAB Media CMCSA CMCSK HHS
6/6/2005 15:00 DBAB Media ADVO PIXR SGA
6/6/2005 15:30 DBAB Media VCI WON

I am trying to use advanced filter to search for a specific group of stocks (eg. I1:I100) in columns D-F and return only the stocks on my list. eg. PIXR is on my list so the filter would return:

6/6/2005 15:00 DBAB Media PIXR

I am having problems getting the criterion correct. Can this be done with advanced filter or is there an easier way?

No luck finding an answer? You could always try Google.