Changing Auto Filter Based On User Inputted Cell
Apr 7, 2007
I have a Workbook that has 2 worksheets, one called CALL QUERY and one called CALL LOG. On the CALL QUERY sheet, cell D9 is a user inputted cell with the cell validation set to list. The user uses the dripdown list to pick an office identifier (3-Letter Code).
On the CALL LOG sheet, I have info about individual calls. Column E has the 3-letter office identifier for each call.
I am trying to find a way to automatically auto filter the list on the CALL LOG sheet with the user input in cell D9 on the CALL QUERY sheet.
I was given the following (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)
View 14 Replies
ADVERTISEMENT
Apr 29, 2014
I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.
What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.
In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.
I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.
[Code] .....
How it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.
Attached Files :
Sales Analysis Template1.xlsm
SOURCEDATAEXAMPLE.xls
View 2 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
Feb 18, 2014
I have two problems:
1) Eliminating doubles when ranking
2) Auto-filtering based on cell values
The first problem is an issue with my formula I believe (I am using a RANK + COUNTIF-1) formula (Shown in tab #2, column L of the attached document)
The second problem will need a macro, but I am not very strong with VBA. In the attached document, I am looking to be able to filter the table in tab one based on the selections I have included at the top. I was able to filter the table based on # of IDs displayed using a formula. The second filter is looking to only show the values in the table that satisfy the condition that column E must match the filter chosen (cell C3).
For example, of the filter chosen in cell C3 is "Yes", I would like the table to rank and display only those rows with a "Yes" in column E.
Attached: Excel_Help1.xlsx
View 2 Replies
View Related
Nov 20, 2008
I need to send an email reminder to teachers on the day they have duty. I know how to set up an email that sends on a certain date, but I am unsure how to code it when the date changes. I have attached the spreadsheet.
In cell A 53, I have the date November 24th. In cell C53, I have the email address of the teacher doing duty that day. In cell F53, I have the date November 25th. In cell, I53 I have the email address of the teacher performing duty that day. On November 24th, the person in C53 should receive a reminder email. On November 25th, the person in cell I53 should receive a reminder email. I will have spreadsheet for every month of the year. So there will be LOTS of changing dates. How do I code this when the date keeps changing?
View 2 Replies
View Related
Jul 21, 2014
I need a macro that simply allows me to open the filter criteria dialog to "contains," then stops, so I can enter the value to be filtered. Macro recorder does not allow me to stop recording at the point the "contains" dialog appears.
View 3 Replies
View Related
Dec 13, 2013
In this example on Sheet 2, I'm trying to hide rows 1-6 if 'E6' is 0, and hide rows 15-26 if 'E18' is 0. These cell values are equal to the same cell in Sheet 1.
I've tried different codes I've found online, but I'm not very experienced with VBA so I can't get it to work. I can get rows 1-6 to hide, but it doesn't do it automatically whenever a zero is inputed into Sheet 1. It only works if I enter something into another cell on Sheet 2.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E6") = 0 Then
Rows("1:6").Select
Selection.EntireRow.Hidden = True
[Code]...
View 5 Replies
View Related
Feb 2, 2009
I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.
Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.
View 6 Replies
View Related
Jun 21, 2014
I'm looking to create a macro which displays a user entry box where the user can input a starting number and then input an ending number. The macro will then cycle through that range inputting each value into a specific cell and perform a print function. It will continue doing this until the last number in the range.
E.g.
Min. Number inputted = 1
Max. Number inputted = 80
Macro will not cycle through that range:
Input 1 into Sheet1 Range A1
Print Page
Input 2 into Sheet1 Range A1
Print Page
.....Continue doing this until it reaches 80
View 3 Replies
View Related
Nov 20, 2013
EXPENSE MASTER 2013 sample color.xlsx
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
View 3 Replies
View Related
Aug 6, 2012
I need to filter data based on criteria based on user input form that is in another workbook. For example, I have a list of departments, I want to create a userform for the user to enter department number and then have excel filter only the data belonging to that department. The person will then update the department records and save the file.
Here is partial code I have been working on but having difficulties:
Dim FilterCriteria
Dim CurrentFileName As String
'Get the current file's name
CurrentFileName = ActiveWorkbook.Name 'how do I call another workbook?
[Code]...
View 1 Replies
View Related
Jun 3, 2009
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
View 3 Replies
View Related
Dec 22, 2009
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
View 9 Replies
View Related
Feb 5, 2014
I'm currently using the following:
[Code] .........
However, i'd like it select the field ie 2-17 based on a year in a cell on another sheet "x" in cell E3.
At the moment i have a row of numbers 1-17 above the corresponding field cell so for example the number 15 is attached to the year 2013 (although this is dynamic).
I just cant work out how to get the autofilter to select the number that matches the cell in "x" E3 with the same value in T2:AJ2 on filter sheet and then filter the column that matches.
View 3 Replies
View Related
May 25, 2013
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
View 1 Replies
View Related
Feb 19, 2009
I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
View 2 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
May 26, 2009
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
View 9 Replies
View Related
Dec 19, 2006
I need to have a User input a date via an InputBox, and then based upon that date, delete rows based on values in Column G. For instance, if a user inputs 12/25/2006, I need to delete all rows that have the date that is less than 12/25/2006 in Column G. There will never be more than 500 Rows of data, and there is data in Columns A thru Z
View 4 Replies
View Related
Jan 23, 2014
How can we program an auto filter a table linked to a cell. For example I have a table with 4 columns (A9:D20) and the 2nd row of the sheet is the filter item (A2, B2, C2 & D2).
Lets say column A has a list of months, column B has a list of names, column C is sales figure, column D is the customer. How can I filter let say for the total sales of Mr. X in the month of January.
I came across a code like this but I don't know how to apply it to my sheet. Of course this code is referring to a different table and links but I just need to learn how to apply this code to my sheet:
Activesheet.AutoFilterMode = False
Activesheet.Range("D2:D60").AutoFilter 1, Activesheet.Range("A2").value
AutoFilter.xlsx
View 4 Replies
View Related
Nov 18, 2009
I'm using auto filter to gather information from a giant list. I'm able to use Subtotal function in Row(1) )to gather all the numbers for a person.What I havent been able to do is figue out a way to get the value of the person that I am auto filtering.
Example
Row 1 contains my subtotal formula's
Row 2 Header Row
Row 3 Auto Filter Buttons
Row 4 All The Data
Column(A4:A65000)has the names of the people So when I use the Auto filter by Name how can I get the first visible cell name to show in (A1)?
View 4 Replies
View Related
Feb 1, 2010
What I am doing is using Column "Z" to determine if there is a value in Column "Q" by using this formula pasted down the entire column "Q3:Q65536"
View 2 Replies
View Related
May 20, 2014
I have a worksheet that contains 3 columns, A, B, C, that I need to run through auto-filter and copy the results from a cell, F2, into another sheet each time the filter criteria changes.
Although the worksheet will contain over 11,000 rows (the attached sample file is trimmed down to around 1000 rows),
Col A will only have 8 different possible criteria for autofilter: 1,2,3,4,5,6,9,10
Col B has around 70 criteria, and Col C has around 700 criteria.
The number of rows in the sheet and consequently the auto-filter criteria will likely change each time (but will usually hover around these quantities).
As an example, here is how I would envision this working for Col C:
1. Starting on the 1st Sheet (named "FW15"), I auto-filter Col C on criteria/value 1
2. I copy the resulting value from Cell F2 of sheet FW15 and paste it into the first empty cell of Col C in Sheet 2 (named "CopiedResults")
3. I return to my first sheet, FW15, turn off the enabled filter for criteria/value, and turn on the next autofilter Criteria/Value of 2
4. Repeat Step 2
.
.
.
Keep looping through Col C to make sure that all auto-filter values have been applied, and all resulting values contained in Cell F2 are copied over to the second sheet.
Likewise, I would need to run through the auto-filter criteria in Col A and Col B, and copy their resulting values (from cell F2) into Sheet2 Col A and Col B.
Attached workbook : autofiltercriteria3.xlsx
View 2 Replies
View Related
Mar 8, 2009
I had already asked a question like this on the forum and was unable to get a working answer. I'm still grateful at the time JBeaucaire spent trying to help me. The solution we had worked in Excel 2003 but when moved into 2007, an error came up. To see the solution and the error, the previous post is at
HTML http://www.excelforum.com/excel-2007-help/663593-adding-function.html
What I'm trying to accomplish is if a person inputs a number into a cell, A1, the value will be added to another cell, A2, then A1 will be cleared. If another value is inputted into A1, then it will be added to the value into A2 on top of all the other values inputted and clear A1 again.
View 7 Replies
View Related
Jun 18, 2013
I am making a list in excel and there are multiple cells where I want the user or customer to fill in their information. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?
i.e. [Manual Entry]
View 3 Replies
View Related
Sep 16, 2011
Is it possible to lock a cell once data has been inputted into it??
I want to make a spreadsheet which has empty cells for reference numbers that once entered cannot be changed unless it is by the administrator.
i also want the spreadsheet to be accessed on multiple pc's by multiple users.
View 9 Replies
View Related
Jun 18, 2013
I am making a list in excel and there are mutliple cells where I want the user or customer to fill in their inormation. For example, In C2, the customer is supposed to enter the name in. How I can put text in this cell that will automatically disappear once they start editing the cell?
i.e. [Manual Entry]
View 1 Replies
View Related
Jul 31, 2013
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
View 2 Replies
View Related
Jan 7, 2009
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
View 9 Replies
View Related
Mar 25, 2009
I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.
View 4 Replies
View Related
