I have a Filter set up across a few columns of data. Lets say one of the columns is called "Hours", and another is called "User". There is another cell called "Totals" outside of the Filter.
What I would like to have is a self-updating Totals cell that will Sum the total number of Hours for whatever the Filter is currently showing.
Examples:
If I set the Filter to show only UserA's data, the Totals cell will Sum the Hours for UserA.
If I set the Filter to show All Users with Hours greater than 2, the Totals cell will Sum all the Hours greater than 2.
If I set the Filter to show UserB and UserC and Hours less than 5...well you get the idea.
Can I write a function that loops through the data that meets the Filter's criteria? Do I need to create a macro to update the Totals cell each time the Filter is changed? Is there an easier way that I don't know about?
what i need to do is display a list of items, with a dropdown box next to each item. the dropdown boxes are identical. for each item in the list (e.g. each musician), the user should be able to select an item from the box ("percussion", "horn", "string").
my problem is that the number of items in the list is not set, so the list of name labels, dropdown boxes, etc needs to be generated dynamically. is it possible to generate a list of labels/dropdown boxes using a loop? (as i understand it, this would involve writing code to write code.)
in a sheet I have two cells using data validation and dropdowns - the source for the first cell is a static named range - no problems. The source of the second cell is dependent on the value in the first cell and the sources are dynamic named ranges. The dynamic ranges in cell#2 is named according to the value in cell#1 and I therefore have referenced the ranges using the INDIRECT function - but this only works with static ranges.
In the data validation source field for cell#2 I have the following formula: =IF($A$1="",the_full_range,INDIRECT($A$1))
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
I have made a table with many dynamic formulas that I want to import into the Name Manager. It has the names in Column A and the "Refers to" formulas in Column B. Unfortunately, when highlighting the cells and using the "Create from Selection" feature of Excel 2010 it puts the Column B cell address in the "Refers to" field, not the actual formula. Is there another way to get these formulas into the "Refers to" field without typing them all out or coping and pasting them individually?
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro. The worksheets are identical and each item is identified by a unique ID. Any ideas in exchange for a 6-pack Budweiser?
I am trying to filter a table of dynamic size. My table begins at P3 (first row of data, not column header) and ends somewhere at the bottom of Column AA. I am getting an error "AutoFilter Method of Range Class Failed." Why? How do I fix this?
Here is my code
VB:
If Target.Range.Address = "$B$4" Then With Sheets("Days Past Due") LastCell = .Cells(.Rows.Count, "AA").End(xlUp).Row MsgBox LastCell ActiveSheet.Range("P3:AA" & LastCell).AutoFilter Field:=17, Criteria1:="MABST" End With End If
There are about five variables which, when entered, should format the row their on. For example, if I select "Waiting List" from a dropdown in column A, the row is formatted to have a blue fill.
I'm currently using this code:
VB: Sub waiting() Dim sh As Worksheet Set sh = Sheets("Applications") sh.Range("A1:X1").AutoFilter Field:=2, Criteria1:="Waiting List" sh.Range("A2:X2" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).Interior.ColorIndex = 37 sh.ShowAllData End Sub
I have five such macros.
The problem I am having is that when there is no entry for the macro to work on, it just formats rows 2-21 anyway, and overwrites any other formatting.
I have attached my workbook so you can take a look and see what I mean. Firstly, select "Waiting List" in cell B2, then run the macro "waiting". It should work fine.
Then, delete the content of B2, and run the macro again.
The range element below needs to be dynamic. A1 and down meaning if i was to do this out of VBA I would hold CTRL+SHIFT+DOWN but i cant do that from macro record as it doesn't work, I'm guessing this Range("A1:A73") is the element I need to change but I don't know what to?
I need to filter data which is 3 months from todays date or older. I'm going to use it in a macro which downloads the information from our accounts system.
I have set up the filter and it works fine when I manually put the Date in, but when I use the date 'formula' for todays date -3 months and reference the cell its in, it does not work. Here's what I have so far :
The filter from the macro : Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("J1:J2"), Unique:=False
The way I work out the date 3 months from now (in cell K2) : =DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
Cell J1 has the relevent Column Header Cell J2 has the following formula : =" < K2 " (there are no spaces I had to put them in so the post appeared correctly!)
I am writing a VBA code to make an advanced filter. Instead of a fixed criteriarange, I would like it to be dynamic. As each time the number of criteria is different.
e.g. Range("A1:G30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("L1:L18"), Unique:=False
I want the criteriarange to change. e.g. It include all the data if I put in 20 data in col L.
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.
I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.
I have created macros that will filter each column based on criteria - for example:
[Code].....
As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.
On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.
I need a function/macro that will find all rows that have a specified value in column A and extract selected columns to a new spreadsheet. More, I need it to do it for every value in column A.
I would also like it to skip creation of new worksheet if value in selected row and column is null.*
I've been trying to combat this problem with advanced filters, which helped, but due to size of the data and range of values in column A it takes an entire day to process manually. Because the data is exported to another program after it's processed, it can't stay in the same sheet, also, linking back to the original sheet doesn't work because the data changes all the time.
We have 2 dynamic ranges (input, output) on different sheets (sample data below).
Sheet1> Input Dynamic range 5 columns
Sheet 2>Output Dynamic range 4 columns - Include only rows IN=1 - Sorted by LEVEL (BIG to SMALL) and BUY (SMALL to BIG) - Keep duplicates
What formulas should I place on Sheet2 avoiding Pivot tables or VBA?
Sheet1> Input (16 data rows) A B C D E Level Buy Sell Firm IN 16620 4.00 null F1 OUT 16610 5.10 0.80 F1 OUT 16600 11.40 6.60 F1 1 16590 24.50 18.60 F1 1 16580 44.90 37.10 F1 1 16570 66.90 59.60 F1 1 16560 84.40 78.70 F1 1 16550 95.00 90.30 F1 1 16540 99.80 95.60 F1 OUT 16530 100.00 98.00 F1 OUT 16611 6.66 0.497 F2 OUT 16600 9.09 1.96 F2 OUT 16589.1 20 12.5 F2 1 16578.2 41.66 33.33 F2 1 16567.3 73.33 65.21 F2 1 16556.4 90.9 84.61 F2 1 16545 98.5 91.66 F2 OUT
Sheet2> output (10 data rows) A B C D Level Buy Sell Firm 16600 11.4 6.6 F1 16590 24.5 18.6 F1 16589.1 20 12.5 F2 16580 44.9 37.1 F1 16578.2 41.66 33.33 F2 16570 66.9 59.6 F1 16567.3 73.33 65.21 F2 16560 84.4 78.7 F1 16556.4 90.9 84.61 F2 16550 95 90.3 F1
I bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.
attached is a spreadsheet effectively replicating a criteria box I’m using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What I’m trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.
Below is the code I’ve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, I’m hoping the macro would only use those 2 rows instead of picking up all 7 rows.
See criteria box on spreadsheet for example. In this particular example, I’m wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with “1”s. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?
Sub RunDynamicSelection_Click() Dim wsSheetDS As Worksheet Dim wsSheetRS Set wsSheetDS = Worksheets("DataSheet") Set wsSheetRS = Worksheets("ReportSelection") With wsSheetDS .AutoFilterMode = False With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _ .UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................
I have a list of codes from which I'm trying to extract all unique entries.
For example, col A has 001, 001, 002, 003, 003, 003, 004, ...
I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.
Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.
The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' )
my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as:
' =counta(b1:namedrange1)
i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
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.
Attached is a spreadsheet I am working on that has a series of graphs with defined dynamic ranges. I am having 2 separate issues on 2 different tabs.
1) Ranges named AirRecBra4 (and Bra10); AirComBra4 (and Bra10); AirRemBra4 (and Bra10) are not pulling in data from the correct data points. Instead of using the last 26 data point, they are currently beginning at the top of the column (currently cells 11 to 36 is their respective columns). The formulas are copy and pasted from working offset ranges in the same tab but are reading differently.
2) On the last 6 tabs beginning with Stn Backlog, I want to have defined formulas beginning in cell B119 and continuing downward to at least F200. However, when I add the formulas, the offset reads the formulas as being data and adjusts for that. I do not know how to adjust the dynamic range to ignore formulas in blank cells.
Global Demand-Capacity Management_working (version 3).xlsm
I am developing a spreadsheet that, once all the code is run has numerous sheets added. On these sheets I have a significant number of rows that contain no data and could be hidden (I dont want to remove them, because later I need to re- import all these rows back to my master sheet). I tried code I found in the forum to hide a row if it is empty but this doesnt work as some of the cells contain formulaes referencing back to another sheet that is hidden.
I am trying, and failing, to write code to hide a row that contains no actual data, but still has formulas in some of the cells.
Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
