Multiple Criteria Filtering With Formulas

May 2, 2013

I have a database with 10 properties of a certain kind of material #.

IE: Material 1206 is Black, SG is 1.2, Duro is 70, Cure, tensile, price, etc.

I want a user to select 5 criteria from a drop-down list and have the 6th drop-down box populate available materials that meet the previous 5 criteria. I am trying to do this automatically and avoid user trying to autofilter (have to make it easy). I've extended the database out to show either a "0" or "1" if the 5 criteria match, but I can not get them into a drop-down list with a formula due to a #REF! error. I am not sure where the circular reference is coming from or what I have to do to change it.

View 1 Replies


ADVERTISEMENT

Filtering Via Multiple Criteria From Different Sheet?

Jul 30, 2014

I have a workbook that has the main user interface on sheet3 and the main database i'm trying to filter on sheet2. Sheet3 has the button that executes the code to open the worksheet with the database and filter based on certain criteria and will filter unique id if a numeric value is entered and surname if text is entered.

Originally this worked ok when i had the button and the' rcrit' cell reference on the same worksheet as the database i'm trying to filter, but i decided to move them to the interface page as it was interfering with other things i'm trying to do.

Ever since then i can't seem to edit it to make it work from the other page! I've tried changing the filter ranges to reflect the new set up, but it seems to no longer be able to recognise numeric/none numeric values.

here's the code I'm using.

VB:
Sub filter()
Application.ScreenUpdating = False
Dim rCrit1 As Range

[Code]....

View 2 Replies View Related

Countif Formulas With Multiple Criteria?

May 26, 2014

I need a formula to count cells E7:E500 that say "Submitted" but only if cells R7:R500 say "PPB".

My original formula below is counting all cells except for the ones that say "PPB"

=COUNT(IF(E7:E500="Submitted",IF(R7:R50="PPB",'E7:E500)))

View 3 Replies View Related

Extracting Unique Data Based On Multiple Criteria With Formulas

Sep 27, 2013

I have data and after doing some data manipulation it looks like this

Job #
Part #
Lot #
Total
Grand Total

[Code]..

What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.

View 2 Replies View Related

VBA For Variable Filtering Criteria

Jan 15, 2014

I have a data set in Excel that starts with row 13 and has 26 columns. The headers are in row 12.

A pop-up user form requires the user to select from a drop down menu i.e Top50, Top100, Top200...

Once this is selected, the script below applies a filter to the header row & filters Column O based on the Topxxx chosen in the user form.

The script then selects the visible cells in selected columns from the data set and copies these to another sheet.

The only thing that changes is the filter criteria i.e the user-defined choice of Topxxx from the user form.

Obviously I would like to try and avoid having to copy copious lines of coding for each filter criteria i.e Top50, Top100 etc etc.

Code:
If UserForm1.ComboBox2 = "Top50" Then
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("A12:Z12").Select
Selection.AutoFilter

[Code] ........

View 1 Replies View Related

Filtering Checklist Based On Different Criteria

Aug 3, 2014

There are 3 types of customers: A, B, and C. For different types of customers, there are different information needed to be filled in. For example, A customer need to fill in question 1,2,3 & 4 ; for B needs 1,2,3,4 &5; for C needs 1,2,3,4,5&6.

How can i:

1) create an advanced filter for selecting different types of customers
2) after selecting the customer type, the spreadsheet automatically generates the right questions?

View 1 Replies View Related

Select The Filtering Criteria From A Drop Down Box

Nov 25, 2008

One sheet will be where someone will be able to select the filtering criteria from a drop down box (ie. Industry, Company, Location, etc) then click an execute button.
Then the macro will go to the second sheet where all the data is, filter the data and create a new spreadsheet with the selected data.

I have been able to create a filtering macro on the data sheet but have not been able to place the macro on the first sheet and have the macro run correctly. I have attached the file.

View 2 Replies View Related

Specific Criteria Filtering To New Worksheet

Apr 1, 2013

I am creating a workbook with multiple worksheets. When row H on the first worksheet matches certain criteria, I want the whole row that this specific cell is in to copy to a separate worksheet of the same workbook in excel so that a list of these rows automatically compiles. How do I do this?

View 1 Replies View Related

Using Days Of Week As Criteria For Filtering?

Oct 20, 2013

Row H contains a date.

This date "example... 10/14/2013" Will always be a MONDAY

I am trying to figure out the code to filter out any Monday that is not of the current (changing) week.

View 5 Replies View Related

Multi Criteria Filtering Listbox

Aug 21, 2007

I am using the following piece of code to 'link' a value from a cell in one workbook into another.

ActiveCell.FormulaR1C1 ="='[filepath]Front Sheet FIT'!r7c4"

However, i have come across a cell which displays the result of a reference to another cell (something simple like =b32). Normally I would just link to b32, however, the macro has to be applied to a large number of documents and in some cases text has been specifically entered, while in others the reference has been used. Is there any way of using VBA to display either the result of a formula (if there is one) or text if this is the case?

View 3 Replies View Related

Loop Filter Criteria: Sheet Filtering It By Different Values

Sep 22, 2006

I'm looping through a sheet filtering it by different values. As of now I just have the same steps repeated 13 times. I want to do a loop instead. So my question is, how do I change the criteria of the filter? I want to perform the specific functions for each JD1 through JD13

Sub Full_S_BreakdownWIP()
jd1 = "FSC"
jd2 = "SA"
jd3 = "SC"
jd4 = "OA"
jd5 = "CA"
jd6 = "DC"
jd7 = "GCA"
jd8 = "PAR"
jd9 = "LG"
jd10 = "RA"
jd11 = "DM"
jd12 = "SR"
jd13 = "USA"
For i = 1 To 13................

View 4 Replies View Related

Filtering Multiple Columns

Jan 28, 2008

I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.

Attached is a sample file.

I am not well versed in Excel and this is my first post so please bear with me in this process.

View 14 Replies View Related

Filtering Multiple Returns

Dec 3, 2008

I have a raw data page which contians peoples name and where they charged thier time. My problem is that I only want thier name to appear once. What type of formula do i need to use to make this happen.

Ex
Employee name Project No. WBS Code Hours
John Smith 123456 1 4
Jane Doe 123456 1 2
John Smith 123456 2 2
Jane Doe 123456 2 1
John Smith 123456 3 2
Jane Doe 123456 3 5

At the end I want to do a count to let me know how many people I have charging the job but I don't know what formula I need to exclude duplicate entries.

View 4 Replies View Related

Filtering For Multiple Pay Types

Sep 9, 2009

I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.

View 8 Replies View Related

Filtering By Multiple Values

Mar 20, 2013

I need to filter 20 out of 100 text values (3 letters each) in a table.

The 20 values are listed in a separate column and I don't know how to filter by that range without manually selecting all of those 20 in the filter?

View 3 Replies View Related

Filtering Multiple Columns At Once

Jul 28, 2008

a formula in excel. here's what i need to do:

column A has the names of deffirent drivers (ex; john, peter, james)

column B has start time (9:00, 10:04, 11:08)

column C end time (9:14, 10:16, 11:23)

i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.

View 10 Replies View Related

Setting Filtering Across Multiple Sheets?

Jul 31, 2013

I work with project data.... I have attached a small sample. I will have data across 8 - 10 tabs with respect to costs associated with different projects.

What I do now is go through each tab and set filters based on what project and date range I am looking for ... if I am not careful, there may be other filters previously set that I do not notice and my data isn't accurate and I may spend quite a bit of time reviewing all the filters and resetting as needed.

But, the filtering will be the same across all tabs (i.e. I may want to see all project data for a single month, or I may want to see all project data for a single project across all date ranges.)

is there a way to create another tab which I can identify the desired filtering than can populate across all the tabs? not all the tabs contain the same number of columns of data, but they do all contain some similar columns that represent the typical filtering range.

View 1 Replies View Related

Filtering Multiple Combobox At Userform

Nov 24, 2013

Is it possible to create a data base with the given sample below?

COLUMN A
COLUMN B
COLUMN C
COLUMN D
COLUMN E

UNIT TYPE
CONDITION
NAME
TELEPHONE NO.
DATE

1+1
SALE
Name1
tel1
3-Oct-12

[Code] ...

The Userform has 2 ComboBoxes and 1 ListBox

ComboBox1 will be the one of the Column A Value
ComboBox2 will be the one of the Column B Value

I did handle that ComboBoxes already....What I need only, when those ComboBoxes been selected the related datas will fill into the ListBox accordingly...

View 9 Replies View Related

Filtering Data Across Multiple Sheets

Jan 23, 2007

I have a workbook that has a sheet for each day of the month. The data on all of these sheets is formatted the same. I have a sheet that is an "overview" sheet. I want to be able to use a filter function (like autofilter does) but have it filter across all of the sheets and display the results on my Overview sheet.

View 3 Replies View Related

Excel Advanced Filtering On Multiple Sheets

Apr 26, 2013

I am doing a project with these instructions:

The state, capital and population of all states with a population greater than 10 million or less than 1 million. (population)
The state, tree and bird of all states with a bird or tree that starts with Eastern or Western (symbols)
The state, population and area of all states with area between 100,000 and 200,000 (area)

I am having troubles with selecting just the three criteria (state, capital, and population) on my Population worksheet. Attached is my work. The copied filter keeps copying all of the data in the data set, and not those three.

View 4 Replies View Related

Filtering Multiple Rows Based On Data In One Row

Apr 26, 2012

I have a 'dump' of a general ledger accounting system....about 20,000 rows. Column A contains the journal number for each set of transactions (there are usually two rows in each set but sometimes three), column C contains the name of the account, and column E the amount. For example:

Row 269 cell A269 = 487 (journal number) cell C269 = Cheque, cell E269 = $50
Row 270 cell A270 = 487 (journal number) cell C270 = Travel, cell E270 = -$50

What I am wanting to do is to extract the sets of journal entries, where the account name (in column C) is "Cheque"....so I would want to extract rows 269 and 270 based on the above example. If I filter using the account name it only gives me one row for each journal transaction - only half of the story. Given that the journal number links the two rows.

View 3 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.

Name
Primary
Secondary
Tertiary

Person 1
x

[Code] .........

View 3 Replies View Related

Sorting (or Maybe Filtering) Worksheet With Multiple Data In Cells

Nov 1, 2008

Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:

Date Name Craft Experience ...more info...

If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)

I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.

Example (Excel 2003): ..

View 4 Replies View Related

Filtering Pivot Using Cell Value (for Select Multiple Items)

Sep 27, 2013

I'm running a pretty huge database with Part Numbers across several Suppliers which fluctuate constantly.

For example: a HEXAGONAL BOLT may have a Part Number ABC123, but depending on the size of the bolt it could be a ABC123-001 ABC123-V28 ABC123-40mm (etc)

There's a lot of data (some of it sensitive) so I don't want the users to see everything - I'm creating a front-end sheet and am looking for a way for a user to type in the Part Number "ABC123" into cell A1, then the Pivot below to auto-update (with Part Number on the 'Report Filter') to show all variations of ABC123, including ABC123-1 ABC123-2 etc - i.e. not an exact match; everything that contains the characters in cell A1.

The relevant information has been ported in and password protected on a sepearate sheet within the workbook, so it's literally a code to make the cell value affect the filter.

View 1 Replies View Related

Copy Formulas Based On Criteria

Mar 12, 2007

how I can copy formulaes in cells B2:S2 down the row if the corresponding A-cell is not empty (contain a string)?

View 3 Replies View Related

Lookup Multiple Criteria & Sum Result On Multiple Criteria

Oct 21, 2006

I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)

This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.

So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30

The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.

View 5 Replies View Related

Conditions / Criteria Based On Arrays With Different Formulas?

Dec 16, 2013

I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).

Here's what i'm trying to condition and than use different formulas:

column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America

[Code] ....

QUESTIONS

1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.

The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.

* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.

2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.

3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C

The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.

I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.

For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.

View 7 Replies View Related

Conditional Format (with Formulas) 10 Cells Depending On 1 Criteria

Oct 19, 2009

I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?

View 5 Replies View Related

Link Data In Worksheets - Add / Deduct Formulas As Criteria Are / Aren't Met?

Dec 31, 2012

What can I do to link my worksheets together? I need the months to link from january to december.

I'm trying to come up with a system to maintain attendance of employees. How do I come up with a rolling system to add and deduct the points as their attendance changes? The goal of each employee is to have the least amount of points as possible, because he/she will be penalized once they receive a certain amount of points.

Let's say that Jane was hired in October. She has accumulated 10 points by December from tardies, call-ins, etc. By the end of January, she has perfect attendance; she is awarded half a point to be taken off toward her 10, giving her 9.5. In February, there is a day she does not show up, so now her total goes up to 12.5 (3 pointed added because of NCNS-no show no call). In March and April, she has perfect attendance, so now her total is 11.5. (half a point allowed for each month with perfect attendance)

By rolling, I mean a formula that will calculate continuously over month after month and have the points roll over month to month.

View 9 Replies View Related

Multiple Criteria SUMIF Using Column As Negative Criteria?

Mar 24, 2014

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved