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......
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.
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?
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".
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.
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.
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.
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.
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.
I 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?
filter 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).
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 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?
I have a worksheet that contains Country Abbreviations and related Codes. Some countries have multiple codes. How would I search for two countries and have it return me only the codes that are related to BOTH countries and not just one country.
Example: Code Country 1 USA 2 GBR 3 HON 4 GBR 4 USA 5 HON 6 USA 6 HON 6 GBR
If I typed in or selected USA and GBR it would only return 4 and 6, not 1 and 2. If I use filters it returns 1, 2, 4, and 6.
type in the three-letter country abbreviations or be able to select them from a list and it return the correct code(s).
I have a pivot table where i would like to filter the values according to top 10, bottom 10 etc. I know that it works with using the filters in the pivot tables (Value Filters - Top 10 ...), but for making it easier and faster to use these filters, I would like to have some preset filter options as buttons or a drop down menu that immediately display them.
So that you don't need to choose the filter values yourself. I need to be able to either filter the e.g. Top2 Business Areas or within one Business Area filter the top 3 Business Groups. I thought about maybe making a drop down menu where you can choose the business areas and then another drop down where you can choose the Filter such as Top10, Bottom 10, Top 3 etc.
I need it to filter on anything beginning with 2613, not just 261302. Unfortunately, I do not know all the possible inclusions or I could just add them individually, so I need a wildcard to add to this filter and I have never done that.
Thus, I ask how to write a wildcard filter that will work in this pivottable.