I'm creating a spreadsheet with data I've exported from a survey.
My data begins column headings on row 10 and spans across several columns. I've applied filters to the headings.
Above column 10 I have some summary data using the countif and counta functions. Is there away to have my summary fields change as I apply my filters to the main body of data in different ways?
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date
-------------------------------------------------------------
John , 100 , $150 , 2011-04-01 17:07:50.0
John , 100 , $250 , 2011-04-01 17:07:50.0
Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase
-----------------------------------------------------------
John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies View Relatedexcel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies View RelatedCountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
is it possible to use two filters within a the same field.
ie. right now i can filter using the top 10 based on a value field.
what if i want to see the top 10 customers profit and along any drop in revenue from previous years..
something like this.
the information below was copied out of a pivot table that included the % difference.
Code and Name
YearCheck
Profit
Difference
[Code].....
I have a worksheet that is unprotected but I am unable to apply filters to it. The option is greyed out if I try and select autofilters.
Another strange thing is that on one of the worksheets when I select Data, Filters there is a tick to the left of Autofilter but it is also greyed out, so in effect I cannot turn it off even tho I can't see the filters!
I have tried to popst a screen shot but unsure how to do it. I think maybe a screen shot would help to explain my issue better so if anyone knows how to do this......
Let's say that A1:C5 looks like this:
Square Red $5
A2 is empty Blue $6
A3 is empty Orange $2
Circle Puple $10
A5 is empty Black $18
A2 and A3 could say "Square" and A5 could say "Circle" ... this is just how I have it set up right now.
In C1 I have a drop down list ... a list of "Square" and "Cirlce".
***QUESTION***
Is there a way for a user to select "square" from the drop down list and in D1 have excel populate a list of all the Squares colors and prices?
Example of what excel would populate in D1:E3:
Red $5
Blue $6
Orange $2
The thing is that I don't think filters is the answer. The reason being is that I want the user to be able to select the item from the list and have excel automatically populate the information. The user should not have to filter the list manually.
In my Sheet I have AutoFilter on multiple columns.
I want to view all rows where in different colomns there are values.
For example
Colomn D, Row 6 has a value
Colomn E, Row 6 has no value, But Colomn E, Row 7 has a value.
I want to view then Row 6 and 7, by setting an OR filter 9 or something like that .
by setting filter on Colomn D with ( not empty Cell ) and Filter on Colomn E with (not empty Cell ) it is only a AND function.
How can I get an OR function. ( Colomn D OR Colomn E )?
I'm trying to modify this code in order to do the following.
I want to choose first column and then the criteria for filter, then i want to choose a second column and criteria to filter the remain values from the first filter.
Here is my
Sub Filter()
Dim Myrange As Range
Dim CriteriaVal As Variant
Dim CriteriaVal2 As Variant
Dim KillColumn As Integer
Dim KillColumn2 As Integer
Dim ActiveColumn As String
Dim AC
Dim LastRow As Long
Dim rng As Range
I have several pagefields. Once something particular has been selected in one of them, I would like the remaining options in the other pagefields to be updated or refreshed showing only those where there is a correlation with the selected pagefield. In other words, exactly how your typical Excel filter works.
I assume there is no option to change the way pagefields interact to be like filters. How could this be done in VBA?
First post so please excuse me if this has been covered before but I am trying to get the results of a dropdown menu in one worksheet to become the criteria for a filter in another worksheet (both in teh same workbook).
I thought I could just add a reference to the dropdown cell into the autofilter (in VBA editor) but it doe't seem to be that simple.
Any ideas?
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.
View 3 Replies View RelatedI need to apply filters based on 2 criteria
Filter1Dropdown SelectionFilter2Dropdown SelectionHeader1Header2Header3Header4Header5Header6
Depending on what is selected in Filter 1, lets say it applies to column F and i get a data set
Now with the filtered dataset of Filter 1, I should be able to apply Filter 2 or vice versa.
Also at the end, I need to release all filters so that i can see the entire data set.
Is it possible to create independent filters. I have data for five departments and sales for different quarters of the years. These sales for the different quarters are independent. But If I select Q1 for first department and Q2 for second department it do not give me the result that I seek i.e. sales for first department for Q1 and Sales for second department for Q2.
View 9 Replies View RelatedAre there any events associated with filters?
If I have two sheets of data, where column A is NAME and column B is GRADE. Sheet1 is MATH and Sheet2 is ENGLISH. The same students are taking both classes.
Is there a way to make it so that when I filter by GRADE in tab 1, tab 2 is ALSO filtered by the same selection?
I am trying to get a simple auto filter on 3 tables in the same spreadsheet.
I have seen this in other documents but have never been able to figure out how to do it.
Cells that need the auto filter are
C8 (Range C9 to C24)
C29 (Range C30 to C45)
C50 (Range C51 to C66)
I made 2 sheets: the first one contains the database and in the second one i want to analyze it. Now i am looking for a special sum- function. I want to sum total revenue for a particular company (criteria 1), in a filtered country (criteria 2), month (criteria 3) and class (criteria 4). I'd like to first execute the three filters (country, month and class) and then be able to total revenue of that particular company. Note that after the filters still several companies are visible in the database. Does anyone know how to calcalate this in cell B9:B12 (Analyze sheet) of my attached file.
View 9 Replies View RelatedI have a question, I have a pivottable with data (only numbers as values). In this pivot there is a report filter which filters current month and last month. Is it possible for the pivot to show the data as a difference between current month and this month and if so where to start with the vba code to let it substract from each other?
View 6 Replies View Relatedfilter a pivot table that contains these 3 column fields: User 1, User 2, and User 3.
However, when I want to see the results for "John" in all those fields, I need to use one filter at a time. Is there any other way to see all results of the Pivot Table where "John" is a User, no matter if he is the 1, 2 or 3 user?
I have A3 and A4 as report filters in the pivot table. Is there any way can combine A3 and A4 into one cell. The new cell is something like drop-down list contains contents from A3 and A4. When picking up the from this drop-down, the corresponding data list will be shown (like the data in B3 and B4).
PivotTable_Filter_01.jpg
PivotTable_Filter_02.jpg
Using Excel 2010
How can I use 2 filters in DAX Calculate?
I tried
MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field] && Tbl2,Tbl2[Field2])) It works with 1 Filter : MyCalc:=CALCULATE([MyMeasure], Filter(Tbl,Tbl[Field])) Just falls down when I add the second filter.
I am doing a budget sheet in Excel for the different expenses accrued by the different classes we offer in our university department (honoraria for guest lectures, tickets to shows or museums, etc.). The sheet is formatted as a table and I have the Total Row enabled at the bottom.
However, we have a reserved budget for different things. For instance, Fall 2013 museum tickets has, say, a budget of $1,000. Fall 2013 honoraria might have a budget of $2,000. I want to be able to filter to see only Fall 2013 honoraria: the totals row will reflect this, but I also want it to automatically tell me how much money is left in that budget (i.e. I want it to know to subtract $2,000 from the totals figure because that is the budget for Fall 2013 honoraria).
It gets a little more complicated because we also have a budget for each class too. One class might have both honoraria for guest lectures and field trips to a museum, and we might say "We don't want this class to spend more than $500." Again, I want to filter to see just this class, and have the Totals row also show me how much money is remaining for this class.
I have an excel sheet with about 3,000 products listed; there are 26 items of data listed against each.
I've been using Data Filters to manipulate the info, but my worksheet has suddenly started running incredibly slowly - up to 7 minutes to implement a simple filter selection.
There was no problem until I made a couple of changes last week:
1. Adding a simple pivot table
2. Using an Index column plus some linked formulae to extract a list of suppliers from the product list.
I assumed that it may be the INDEX and related formulae or the pivot table that were causing the Data Filters to run slow, so I deleted them, but to no effect. I've even gone so far as to copy the raw data into a new sheet, but it's still running incredibly slowly when I try to filter. I am convinced that the problem is related to some hidden legacy of the table or functions, as it only started once I'd added these, but I don't know if this is plausible in reality and if so how to get rid of it.
I have an excel database with comboboxes. I want to be able to filter the data, therefore I am using the autofilter function.
1) When I filter, the comboboxes above a data section that is to be viewed end up at the first row, and comboboxes below end up in the first row following the viewed data set. Is there any way in which I can hide comboboxes when their rows are hidden? Today I use the form comboboxes, but I can use the control toolbox ones if necessary. Also, I can use some other function than the autofilter function if necessary.
2) Is there any way to make the filter function take information in comboboxes into account as well? For example, if I filter by weight and one option is available in three different weightes (displayed in a combobox) I still want that to be seen following filtering (can I perhaps write information in the cell under the comboboxes? If so, can the filter function take several data in the same cell into account?).
I would like to obtain a list that is the equivalent of the list that I get when double clicking in the cell of a PivotTable.
IF i have for example
I would like to obtain all the lines that have "d" in column 4 in one table.
Is that possible with just formulas?
I got the number of occurrence in the series by doing a countif on column 4
I am trying to understand some formulas that include indirect, address, small, if and row but don't really get them...
I got this far but it doesn't work:
=INDIRECT(address(small(if(Fahrzeugpark!$AD$5:$AD$5000=AA21;
row(Fahrzeugpark!$AD$5:$AD$5000));column()-1)+1-row(AQ1;2)))
I am trying to arrange data (pay by week) for regions and sites within a region. In the first Report filter, I select the site, and the second report filter gives me all of the sites - not just those in that region.... How I can either arrange my data or change my pivot, so only the appropriate sites show under the region?
View 1 Replies View RelatedI have a column with 3 different types of data in 30000 cells in Column A (Example model attached). This contains data types (i) Alpha Numeric Data (ii) Numbers only (iii) email ids
Unique identifiers for the above 3 are:
(i) Alpha numberic data : ABC
(ii) Numbers only : 1375
(iii) email ids : @
seggregate these data into 3 different columns.