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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
Jul 9, 2009
I have tried many options but can't seem to figure this out.
I have two tabs:
Trial balance and Analysis
On the Analysis sheet, I have a cell where I want it to look on the Trial balance sheet, find all codes these codes (7813.1, 7813.2, 7813.89) which are in column A:A, then give them the totals of what is in G:G.
View 12 Replies
View Related
Dec 14, 2013
When I enter my formula on the same sheet the data is on, I get the answer I am looking for, but when I enter it on a separate sheet, I get #N/A
Here is the formula on the sheet that contains the data: (works perfect)
=INDEX(K1:M144,MATCH(H17&I17,K1:K144&L1:L144,0),3)
Here is the formula on the separate sheetreturns #N/A)
=INDEX(List!K1:M144,MATCH(D4&E4,List!K1:K144&List!L1:L144,0),3)
View 5 Replies
View Related
Jun 16, 2014
I have a spreadsheet with 12 tabs (one for each month of the year). What I need is a macro/function that on execution will pull all rows from each sheet that has the word "overdue" in cell E from E9 down. I need the whole row of data being taken into a new sheet.
So for example, in each sheet there could be the word overdue appearing in 30 out of 500 rows I need those complete rows (A to Y) being put into another sheet for ease. At the moment I am filtering each sheet and copying and pasting into a new sheet for each bloody sheet (LOOOONG way).
The worksheets are titled: Jan 14, Feb 14, March 14, April 14, June 14, July 14, Aug 14, Sept 14, Oct 14, Nov 14, Dec 14
View 6 Replies
View Related
May 23, 2008
I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.
So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5?
I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.
View 14 Replies
View Related
Jul 27, 2013
I am working on a project to automate the import of data and fixing the formatting.
The data contains the date in column A and time in column B. The time is in increments of 15 min for each day. The rest of the data is in columns C:F. Some of the dates don't have all of the times within the day (so instead of 96 rows of data per day, there maybe 80 rows). I have a second sheet of just dates and times that has the correct amount of rows for each day. What I need to do is have the rows data that matches in both the date and time column from sheet 1 copied over to the corresponding date and time on sheet 2. Blank data rows on sheet 2 are fine. I have found some stuff on .Find, but the stuff I found was for specific searches.
Example
Sheet 1
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 01:00 AM 9 10 11 12
Sheet 2
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 12:30 AM
7/27/13 12:45 AM
7/27/13 01:00 AM 9 10 11 12
View 1 Replies
View Related
Feb 23, 2012
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
View 2 Replies
View Related
Mar 17, 2014
The sheet is pretty simple - I am trying to track the division of a personal estate.
First page has columns for item number, then item, drop down menus for location in house, value and "picked by". So 5 total.
Second page is a "summary of division" listing number of items, median cost of items and complete value. Below is a breakdown of the total value of items that each individual selected.
While the majority of items have yet to to priced - the price range is there - so the format (whatever is it) should work.
View 1 Replies
View Related
Jan 7, 2009
Once again I need your help to solve my problem. I have attached one zip file which contain 2 excel file.
1. Master.xls
2. Corporate Documents Master file- Till DEC 08.xls
What i need to do is that ? i need to execute the macro from master.xls. The macro need to ask the input file, the input file will be 2nd excel file.
Based on the "TAG" column value in the master.xls file it need to check that value exist in the second excel (Corporate Documents Master file- Till DEC 08.xls) column "INVESTOR'S NAME" for example :-
If it check for TAG value :- PVT LTD
Matching Record will be :-
Record 2, 7 , 14 ... etc.
A column need to be inserted next to it and name it as "NEW INVESTOR'S NAME", What ever record matches need to remove the TAG value eg. "PVT LTD" from that name and copied to the "NEW INVESTOR'S NAME" and high light that changed field with red colour. The record which doesn't contain any TAG value mentioned in the master.xls file need to be moved like that to "NEW INVESTOR'S NAME"
After that we need to sort column "FOLIO ID"+ "NEW INVESTOR'S NAME"+"Documents", if you find any similar record we need to move all the record it new sheet we name it as "Similar Records" leaving one record in "Corporate Documents Master file- Till DEC 08.xls" sheet. In "Similar Records" sheet at end we to add on column name "Master Record Row Number" there we need to enter the row number where that similar record in the "Corporate Documents Master file- Till DEC 08.xls" sheet.
For eg :- .....
View 14 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
May 5, 2014
I have the following macro so that we can use the outline feature without having to unprotect a sheet.
Private Sub Workbook_Open()
With Worksheets("Schedule")
.Protect Password:="APQP", Userinterfaceonly:=True[code]....
However once the macro is run, filtering will not work without unprotecting the sheet first. If you choose not to run the macro, filtering will work without unprotecting the sheet however the outline feature will not. what I need to add and where to make both the outline and filter features to work without having to unptotect the sheet?
View 1 Replies
View Related
Nov 14, 2009
I need to create report (just summing up) based on values in two different columns. I need a VBA to do it as the column values may vary at any time.
I have attached a sample workbook for your kind reference.
View 14 Replies
View Related
