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 Repliesbasically i have a macro to filter a lot of data based on peoples names but the people keep changing so I dont want to go into VBA each time to update the name list int he macro.I've created a list of names in a sheet in excel but not sure how to point the macro to filtering by that instead, heres what I have:
VB:
ActiveSheet.Range("$A$3:$H$61").AutoFilter Field:=7, Criteria1:=Array( _
"Name1", "Name2", "Name3", "Name4", Operator:=xlFilterValues
ActiveSheet.PageSetup.PrintArea = ""
[Code]....
I have named the database list for each name and its on a sheet called Team_List
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 = " "
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet
[URL] .....
Windows 7 (32bit)
MS EXCEL 2010
I've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.
View 2 Replies View RelatedI 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 RelatedI have to filter a data based upon the selection list value.
Ex:
Assume that i have 4 values listed in my SELECTION LIST
INDIA
US
UK
AUS
If i select "US" in selection list ...Filter has to take this dynamically and filter the data..
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
I have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
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.
Is it possible to write a macro that will rename a worksheet based on the value of the cell?
For instance, if cell a1 has the value Test, the worksheet should be named Test. If I change the value of the cell to say Test 1, the worksheet should automatically rename itself to Test 1.
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies View RelatedI have a macro attached with attached file; it will list the files then will re-name the files based on other cell
example;
Cell A2 = New.mp4
Cell B2 = ggffrr.mp4
so the file New.mp4 will be renamed to ggffrr.mp4.
it is working fine but the location will be always desktop or documents location
i need from the macro to rename the files and to keep them in the same location.
I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.
I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?
I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:
ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"
If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.
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]....
I have an Excel file for work that has the following:
Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)
What I have currently is that if it says:
John Doe 15:00 5
It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.
I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.
Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.
I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.
Here's how it looks when I'm ready to start the macro:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a
[Code] ......
And I need it to look like this:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d
[Code] ........
if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)
View 12 Replies View RelatedI 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.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I want to filter a range (A18:Q2300) based on the content of cell H4 (for column A) and cell h6 (for column B).
I have spent prpbably the whole morning going through the archive looking for an answer and I know I gonna scream when one of you kind folk answer this for me.
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
I have a table which says that this is the amount of coloured cells we have in another sheet.
For e.g.
Field Name Code A
Item Description 5
Now, the item description column has 5 cells in another sheet which are filled in with "Yellow" Colour. So what i want to do is to click on this 5 in sheet 2 in this case as per the attached sample which takes me to the filtered result on sheet1 of 5 yellow coloured cells under the column of Item description including an additional filter of Code "A"
I regularly receive many files that require renaming. Column A contains the current name and column B contains the desired name. Could anyone tell me how to use these lists to rename the files?
View 4 Replies View RelatedI have a spreadsheet I use to track supplier information. On the second tab of the spreadsheet, I have a table that is auto-populated based on the first spreadsheet and is linked to a KPI reporting form that I run for each supplier every month. The information on the report changes as a supplier is picked using a simple data filter. I would like the supplier name on the KPI form to change to the supplier that is picked from the filter on the first spreadsheet.
View 4 Replies View RelatedI 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
i have been able to get most of my VB work on my own (i'm learning). Currently I'm having a bit of difficulty in hiding a row based on a cell value, potentially due to cells being merged.
I'd like to select from a dropdown cell to filter on a row's value. Attached you will find a sample sheet that works named "Working" in it's current state and the one "TEST" that does not filter every other line item.
I have an excel file that gets records from access database. I have a field named "Class" in column C which starts at row 4. I want to filter the records in such a way that only those records with Class equal to whatever value I put in cell B2 will be shown.
For example:
A B C
REPORT
Filter Class: _______
ProjID Name Class
001 Project A 4
002 Project B 4
003 Project C 4
004 Project D 8............
From what I understand there's no way to do this without using macros. I would perfer not to use macros, but I need my PivotTables to auto-refresh anyways and apparently that will require a macro, so oh well. I'm very new to macro coding so I can't seem to successful apply any of the previous threads about this to my sheet.
I just need my pivot table on sheet "Customer" to filter the customer field based on the value in cell C1. Also I need to make sure the sheet doesn't "freak out" if the value in C1 is either blank, or is a customer value which doesn't exist in the table. Preferably in this scenario it would display nothing on the pivot table, but I don't know if that's possible. I want the sheet/macro to allow an invalid filter value in C1 just so the sheet doesn't lock up until it is corrected.
I also need my workbook to auto-refresh all the pivot tables is that's easy to code in as well.
I try filter a pivot table based a cell.
[Code] .....
Attached File : FilterPT.xlsx