I'm working on an excel sheet to display technical problems in an office. I want to use a filter in order to not display problems that were resolved (by applying a filter to show only "0" on the "problem duration" column that displays "hours problem was up after solved" and "0" if not solved) as the point is to display only non-resolved issues.
I wanted this filter to be re-applied constantly, meaning once I fill the time and date it's solved and the formula calculates the time it was up, this row will automatically vanish due to the filter applied.
I found this thread on your site Refresh Autofilter Automatically from the resolved problems so we need to remove the filters once in awhile. With this after the filter is removed, you cannot setup a new filter at all (it just doesn't seem to allow you, erasing it after it is being applied). I need to not only be able to setup a new filter, but also that this new filter will be reapplied automatically all the time (temporary disable of a filter is also an option, but I do not know whether it's possible).
I do have Excel 2007 on Vista, but I also need this to work on Excel 2003 with XP.
I read your reply to the thread below, and used your Worksheet_Calculate routine from it. It only works for the first sheet in my workbook. I have multiple sheets in the workbook, and when I run the routine on an activesheet other than the first sheet in the workbook, it doesnt work.
Is there a way to get this routine to work on a worksheet other than the first one in the book?
I'm trying to use " autofilter". I put the code as Worksheet_Change(ByVal Target As Excel. Range)
so it'll update by itself. My criteria are on row 5 and all my data is below row 7. Row 7 contains the headers. This all works fine for text-fields. My question is if someone can get it working with numbered fields?
I have a userform that allows you to select months to apply to an autofilter, In the code below ComboBox2 is the beginning Month, and Combobox5 is the last month in what I want to be a range of months. I need it to account for the months inbetween. Currently using my code it only selects the 2 months, not those inbetween.
Code: Private Sub RunButton_Click() Unload DataReport Rows("1").AutoFilter Field:=24, Criteria1:=Me.ComboBox2, Operator:=xlOr, Criteria2:=Me.ComboBox5 Call Filtered Call AMasterBuild End Sub
Setting up all 50 states was not so bad, and I tried a Pivot Table but I have very little experience with them and could not figure out how to show only the nonzero states. So, I looked up how to filter the pivot table and discovered I could just filter the state data without the pivot table.
So, I put all 50 state abbreviations in one column. In the next column I put countif formulas to count how many customers came from each state. (In the meantime, I learned how to use an indirect formula to pull the state ID from the first column and copy it down and have the formula advance for each row... cool!) Then, I filtered the columns on the count twice. First, in descending order; then, without the zeros.
However, the weak side of the filter is that it does not automatically update when a new state is entered. I have to go and manually filter again. So, is there a way to get the filter to update dynamically? I know that a pivot table is dynamic but I have a lot to learn and I can probably browse around and figure out how to show only the nonzero states. Once I learned that, would it dynamically update when a new state is added?
Dim MyData As Range Dim MyResult As Range Set MyData = Range("E1:E1000000") Set MyData2 = Range("F1:F1000000") Set MyResult = Range("J4") MyResult.Select Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"
Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.
Use filter for 2 columns of a CSV file, column A and Column B for certain words. Say I apply a text filter: contains "word1" or contains "word2" to Column A. Then I get the results. After that, I remove the filter to Column A then apply the same filter to Column B. The thing is, I don't get the results in Column B, instead, I get the cells in Column A wherein Column B is filtered PHOTOS BELOW:
The data that I get for this filter are: And Word1 you Word2 is blank
Apply filter to Column B, get the cells filtered in Column A:
The data that get for this filter are from Column A (which are cells filtered from Column B filter) And Word1 you Is the blah Word is all
The results should in a single Column for both filter:
I have more than 100 files that needs to filtered this way, is there any way I can do this automatically?
Here is what the file looks like:
The results I hope would be possible:
Automatically without having to open all the files and have the results of all of it in 1 single Excel file?
----or----
If the above is not possible, open each file and automatically apply the filter into a column in just 1 click and without having to type the same filter for each column.
I found a code to combine all CSV files into 1 Excel file, but half of my files has more than 500k rows and the Excel's limit for rows is around 1million+.
I have two worksheets, one that I update information on and the other which just basically points to that information. The second one is for viewing by a different department and has an 'Autofilter' applied so they only see certain information. This works fine as such until the data is changed in the first worksheet, at this point the 'Autofilter' doesn't automatically update, I have to take the filter off then put it back on again.
Is there anyway to ensure the autofilter updates automatically or is there an alternative way of doing this,
I have a worksheet set with Autofilter. This worksheet is populated by formulas taking data entered on another worksheet.
I want the Autofilter for the first worksheet to automatically refresh each time I view that worksheet or as data is entered on the supporting worksheet.
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.
The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...
I know how to set a conditional for a cell to change a color when i apply a specific value, but how about if i want to set E2 cell to change to green when i place a 'X' on F2?
Pretty much I want 2 columns that say Yes and the Other No. When i place a X on Yes that other cell turns green, if i place a X on No that other cell turns red.
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text End If End Sub
I want to get the dynamic sum of a column after a change in the auto filter. In the attached excel after filtering the column 1 and column 2, i want to add all the values of column 3. This should happen dynamically.
For E.g. : Column 1 =0 Column 2 =a (Refer to the attached excel sheet.)
Then Total should return 200 (i.e. Sum of column 3 after the filtering).
I'm coding a userform where there are some comboxes which are popolated by values coming from Sheet2. Up to now I work it out (maybe its not elegant but it works).
Now I would like that when the user selects one combobox the values of the other comboboxes are set accordingly to the grid in Sheet2.
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
I have a spreadsheet i would like to have changes tracked to automatically. I know it's only a matter of clicking a few buttons to get this to happen, but i was wondering if there is a way to have excel do this by default when i open the file. I would also like to have the changes highlighted automatically.
Is there a way that I can add a statement or change my code to automatically have the checkbox checked if a certain value in a cell is greater than zero?
This is my If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
I want the value in cell D12 to change automatically when I put in certain values. If I type in "Weather" I want the cell to change to say "WOW". If I type in "Bell Run" I want it to change to say "BR".
I want the 2 graphs in "Graph" worksheet to change automatically when a row is inserted in "Data" worksheet . Every time i have to change the graph manually to contain the latest 20 days value. I want some offset or something which can be put in the range provided below to do the work.
Chart Data Range =Data!$A$1:$A$22,Data!$F$1:$F$2,Data!$L$1:$L$20
Legend entries (Series) - Series Name =Data!$F$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$F$2:$F$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$F$3:$F$23)
Legend entries (Series) - Series Name =Data!$L$1 ( this will remain constant everyday as this is a header field)
Series Values =Data!$L$2:$L$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$L$3:$L$23)
Horizontal (Category) axis series -Axis Label Range =Data!$A$2:$A$22 ( this should contain last 20 days range, for example if a next row is inserted tomorrow then it should automatically change to =Data!$A$3:$A$23)
Basically, at month end, my company will create a new folder with the name as YYYY_MM (e.g. 2012_11). In this folder, a new file will be created as: earnings_YYYY_MM (e.g. earnings_2012_11).
What I want is to automatically update the link to the most recent file: earnings_YYYY_MM, whenever it is available. For example, next month there will be a new file earnings_2012_12 created in the new folder 2012_12, then the new file will be linked with.
I'm attempting to get a column of due dates to update automatically every time a date changes. The due dates are in B3:B15. There is a header in B3. The day after the actual due date, it is automatically changed to the same day on the next month (showing the next due date). I need the spread sheet to automatically sort the due dates from the closest due at the top, to the furthest due date from today at the bottom. Running it manually, or sorting it manually every time won't work. This is for an elderly couple who are having problems keeping track of everything each month. I just need something that will show them the upcoming bills and how soon they are due when they open the workbook. Anything other than opening the workbook is going to be too complicated. I'm new to macros. I've tried to modify some I've found in other threads, but no luck (lots of error messages). I don't understand it enough yet to figure out what I need to change. I've attached a sample sheet that shows the part of the workbook I'm having trouble with.
I have a speadsheet with a column of student names with corresponding columns of classes and class times. The class times are on a rotating schedule and will be35 minutes later every week. Groups A to D have rotating lessons at 8.55, 9.30,10.05, 10.40 and groups E and F have rotating lessons at 11.45 and 12.20. I need to print slips with student names and class times each week. I want to be able to update the lesson time for the next week automatically. The only way I have thought to create a custom sort list with lesson times and I drag and fill series for new lesson times. However, the list doesn't return to the beginning of the series, it creates a new lesson time. My question is, is there a better way to update the lesson times?
