Filter Data Based On Values Located On Another Worksheet
Dec 28, 2011
On a first sheet, I have a list of cities where physicians travel along with an autofilter. On that same worksheet, I have a cell where users can input a zipcode.
On a second (hidden) sheet, a function calculates and displays in column F the distance in miles between the user-input zip code and all the cities in 4 states. The cities are listed in column D.
I need to filter all the cities on the first sheet (the ones where the docs travel) based on their distance from the user-input zipcode - namely all the cities located less than 150 miles from that zipcode.
View 4 Replies
ADVERTISEMENT
Jan 5, 2008
Is there way to take the values of a listbox selection located on a Worksheet and call that value when I initialize a Userform?
Or, if not, is there a way to declare my listbox selection as a public variable that can be accessed from any module of my VBA project?
View 9 Replies
View Related
Jul 11, 2014
I currently have a macro that looks at one value and copies the data to another worksheet.
I'd like to modify it to look at two different values and then copy the data. I've tried modifying the VBA code, but can't seem to get it to work. In the "ETF Report (1)" tab, i want it to look at the "ETF Report" and "PO No" values.
The Final Version tab is what i'd like the results to look like.
View 4 Replies
View Related
Apr 17, 2013
I have a text box located on sheet1 and on sheet2 I have a table with several data. I would like to filter the first field of the table based on the info that an user enters to the text box. Do you know a code that can do that?
View 3 Replies
View Related
Feb 20, 2013
I have 2 worksheets. Sheet1 has a list of data (Range = "ClosedOpps"); Sheet2 has a Region dropdown list with Domestic and International as choices. What I would like to happen is when I pick a value in the dropdown list, ClosedOpps list filters by that value. Region is the 5th column in Sheet1.
View 1 Replies
View Related
Apr 16, 2008
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilterMode = False
Range("d6:g6"). AutoFilter
Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
Criteria2:=Range("e2")
I've attached the file also.
What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??
(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
View 8 Replies
View Related
Mar 14, 2012
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
View 4 Replies
View Related
Feb 10, 2010
i have a table and have some columns.
i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.
How can i implement an OR filter ?
View 2 Replies
View Related
Apr 11, 2013
I am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.
ColA = Input list
ColB = Filtered list
ColC = Master list
Items:
Orange
Red
Blue
Yellow
Green
ColA contains the colors i manually enter
ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
ColC contains ALL the items above.
Two Examples of a functional worksheet:
ColA............ColB
Orange........Blue
Green..........Yellow
ColA........ColB
Orange....Blue
.............Yellow
.............Green
ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA
View 8 Replies
View Related
Apr 24, 2014
I am trying to create a macro that will allow the user to select choices from combo boxes on a userform. The choice of one combo box determines what will be shown in the next combo box and so on. This will occur a set number of times (depending on what they are looking for), at which point the list of possible choices will be presented.
I populated one combo box in UserForm_Initialize() but then I might have to change the others with Combobox1_Change()...
View 4 Replies
View Related
Sep 21, 2006
I am trying to filter data on one of my worksheets the column format is as follows:
supplier,product,price
the price it totaled at the bottom of the price column. I need to be able to filter by supplier to create a purchase order leaving all products by that supplier in the rows allong with the product name and the total price for that supplier. using the autofilter i can filter by supplier but the total cost remains the same. it is the Total cost that is causing me problems.
View 2 Replies
View Related
Apr 2, 2007
I have a table of data, every entry in the table is different. I need a way in a single cell to determine what column the data is in (and a way to get the row it's in would be nice too), some way to do VLOOKUP and HLOOKUP in reverse.
In the attached example I need a function that can take dog and return "1", "A" or even the header "Animal", if another similar function can return the row, that would be nice also but if I can get the column I can get the row. Example
I've tried a few things with creating an array formula to do matches across multiple columns and I can make a multi-cell array that will flag the right column with the right column number, but any attempt to sum/max/match this array formula returns 0 or #N/A
View 5 Replies
View Related
Jan 20, 2012
I have an order request sheet with many diffrent types of products. I would like to filter only the data with amounts greater than zero to show up on another sheet without having to use advance filter every time. That way the sheet is not cluttered with products that are not sold.
View 6 Replies
View Related
Feb 12, 2008
I have problem with a database, I want to export data that is linked on a autofilter to a new worksheet,but I don't achieve in doing this. basically I want to copy all the data of a column which is selected by the autofilter (but not on the column i want to export) to a new worksheet; the layout of the database is fixed and may not change. I have made a replica of the database showing the same kind of layout but with less data so it would like to export all data in column D that is actived from the active autofilter (which can change )
View 4 Replies
View Related
Aug 11, 2009
I currently have a button on a worksheet that loads a userform. I have code written that's not in a in a separate module but in the user form itself. It uses the information entered on the user form screen. When I try to reference data that's on a sheet other than the one that the button is located on, I get a "select method of range class failed" error. I assume that this problem has to do with the button macro being located on a different sheet than the data. Is there an easy fix?
View 4 Replies
View Related
Sep 1, 2007
I have a database in Sheet1, and need to make a drop-down list for each column title, so that it would filter out the necessary letters or words. E.g. I type "au" and see the words starting "au" in one single list (like aura, aubergine, etc..)
View 5 Replies
View Related
Jun 2, 2008
can excel do this (see the attachment pls). if possible can someone show me how to do that. i am new in excel vba.
View 9 Replies
View Related
Feb 5, 2009
I am wondering, in excel 2003 is it possible to randomly select two numbers from one column, say column b9:b45, that are random numbers, and selected by two different font colors. In other words: b9=1348, b10=1349, b11=1350, b12=1351, b13=1352, b14=1353.
Say we press a macro button, and in field B50 one radom black colored number is selected of a field of say 30 numbers that are all black in that column, and in b51, one red number is selected, in a field of 30 red numbers. We want to used this macro to select winners simulating a raffle drawing.
View 4 Replies
View Related
Mar 7, 2008
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
View 2 Replies
View Related
May 16, 2014
I am trying to improve a spreadsheet that I have to use everyday. I have a list with varying number of entries (different length every day) that has a number of columns including a column that contains machine codes (say 1,2,3,4,5...).
Each machine is programmed by a different person (say Andy,Ben,Craig,etc.).
One programmer can program more than one machine so for instance Andy is interested in programs for machines 1,3,5 and Ben in machines 2 and 4.
Every day we send a list of jobs that require programming which is pre-filtered for each programmer so if I send the list to Andy he only sees rows of data with machine codes 1,3 and 5.
I am trying to make a drop-down where I pick the programmer's name and the list automatically gets filtered for the machines for that specific person (e.g. 1,3,5).
I believe this could be done with advanced filtering but the problem is that the list length changes every day and the data is pasted into the spreadsheet from a different CSV file so I cannot define a set range for advanced filtering because it does not accept blank rows...
I am attaching a screenshot : list.jpg
View 1 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Nov 15, 2012
On sheet1 I got a lot of columns with data, starting from row 8, which is the header.
On sheet2 I'd like to retrieve the filtered data, based on the value mentioned in cell A1 (on sheet2)
So if for example cell A1 on sheet2 shows "Peter", the code should filter the data on sheet1 with "peter" in the header.
The filtering should be based on cell color (red in this case)
The filtered data should then be copied to sheet2 starting from A3
View 3 Replies
View Related
Apr 24, 2014
I'm trying to add a line of code to filter some data based on a date in the range cell C3.
This is my code;
[Code] ....
It doesn't seem to work.
View 4 Replies
View Related
Feb 25, 2007
I have a list of items in column A. And a "flag" in column B. I have attached a small sample. What I need to be able to do is "Hide" all the items in column A where there are no flags for that item in column B. This I can do. The problem is that if a flag shows for an item, I want to show ALL of the rows containing that item, even if there are no flags for some of them, and hide the rest.
View 3 Replies
View Related
May 2, 2014
I just want to filter and sort the data based on names and hours.
Here it goes:
1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:
-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"
For example for UK and M UK:
In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..
I've attached my file : Filter and Sort.xlsx
View 3 Replies
View Related
Jul 31, 2008
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
View 9 Replies
View Related
Feb 14, 2013
I found this code and am trying to use it to update the filter in my pivot table (sheet 6), based on the data validation selection in sheet 1, but when I make my selection on sheet 1, nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Region"
[code]....
View 3 Replies
View Related
Aug 13, 2014
I have a 'Summary' worksheet which is shown as:
August
September
October
Example 1
1
4
5
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
View 1 Replies
View Related
May 22, 2013
I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.
View 7 Replies
View Related
Mar 26, 2013
I have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
View 1 Replies
View Related
