Unable To Use Filters

Jul 10, 2007

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......

ADVERTISEMENT

Advanced Filters - Having Multiple Filters And Conditions (Unique Count)

Jun 7, 2006

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 Related

Pivot Filters To Change Simultaneously With Other Pivot Filters?

Jul 1, 2014

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 Related

Use Two Filters Within A The Same Field?

Jul 18, 2014

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].....

View 1 Replies View Related

Filters The VLOOKUP In Vba

Jan 2, 2008

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.

View 9 Replies View Related

Filters With OR Function

Aug 11, 2008

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 )?

View 9 Replies View Related

Filters And Criterias

Feb 20, 2009

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

View 9 Replies View Related

Using Pagefields Like Filters

Jul 26, 2006

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?

View 7 Replies View Related

Drop Downs & Filters

Dec 16, 2008

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?

View 10 Replies View Related

Macro For Filters With Exceptions?

Jan 20, 2010

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.

View 3 Replies View Related

Subtotals In Groups Vs Filters

Dec 12, 2008

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 Related

Summary Fields With Filters?

Feb 11, 2010

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?

View 11 Replies View Related

Mutiple Filters Using VBA Or Non VBA Method?

May 8, 2012

I 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.

View 2 Replies View Related

Creating Independent Filters?

Aug 1, 2012

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 Related

Filters Across Multiple Sheets

Jan 19, 2007

Are 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?

View 9 Replies View Related

Multiple Auto Filters

Sep 25, 2009

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)

View 9 Replies View Related

Sum Revenue After 3 Filters And 1 Criteria

Aug 6, 2006

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 Related

Subtract Data Pivot From Different Filters

Apr 4, 2014

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?

View 6 Replies View Related

Pivot Table Filters With OR Operator?

Nov 26, 2013

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?

View 3 Replies View Related

How To Combine Two Filters Into One In Pivot Table

Dec 2, 2013

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

View 3 Replies View Related

Excel 2010 :: DAX Calculate With Two Filters

Dec 24, 2013

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.

View 7 Replies View Related

How To Overlay Different Totals Figures With Filters

Mar 13, 2014

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.

View 4 Replies View Related

Data Filters Running Very Slow

Oct 5, 2009

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.

View 12 Replies View Related

Filters Mess Up Combo Boxes

Nov 13, 2009

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?).

View 10 Replies View Related

Extract Lines From Table Without VBA Or Filters

Mar 25, 2013

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)))

View 2 Replies View Related

Pivot Table With 2 Report Filters

Apr 16, 2013

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 Related

Data Segregation In Different Columns Using Filters

Nov 21, 2013

I 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.

View 1 Replies View Related

Only Return Values That Satisfy Both Filters?

Jan 30, 2014

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).

View 1 Replies View Related

Dropdown For Preset Filters In Pivot

Jul 8, 2014

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.

bSample_Pivot.xlsx

View 3 Replies View Related

PivotTable - Adding Page Filters

Aug 11, 2014

First, the code:

[Code] ....

My problem is with this part here:

[Code] .....

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.

View 1 Replies View Related

Range Of Number Sets Filters

Sep 28, 2007

I've got a range of number sets that i would like to filter. I want to be able to remove sets based on whether they have sequential numbers in them. i.e remove all sets with 4 consecutive numbers within the set. eg. 1,2,3,4,15,34 or 5,6,7,8,12,17 if you get my drift.
I've attached a sample file.

View 11 Replies View Related

Clearing Filters And Returning To Cell A1

Feb 27, 2009

I am not sure if this is doable in VB or if there is an excel function to do this but I am using a rather large worksheet that is shared amongst my staff. I would like to have the sheet clear any filters that have been used by one member and for the sheet to save with the cursor in the home cell (A1). This way the sheet is ready for the next user. Can this be done with VB?

View 8 Replies View Related

Using Advanced Filter In Combination With Other Filters

Jul 10, 2009

I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.

I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.

I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.

View 7 Replies View Related

Clear Filters From Inactive Sheet VBA

Dec 15, 2009

In VBA, is there code to clear all filters from an inactive worksheet or does the worksheet need to be activated?

View 14 Replies View Related

Linking Filters And Copying Results

May 27, 2008

what i have is a workbook with 4 worksheets. each is a list of tapes that i use for back ups.

each worksheet has a auto filter so that i can filter by the day i need to use the tape. i.e. when i select monday it displays mondays tapes only.

what i want to do is take the result from this and copy it into another sheet so when i select tuesday it copys onto a 5th sheet in a specific area.

i would also like it to change the day on all sheets filters from a single drop down. i cant put everything on one sheet as there are duplicate tapes and days.

View 10 Replies View Related

How To Retrieve All Pivot Table Filters

Nov 23, 2011

I can't know what fields will be filtered on so, so I need to extract all filters at run time.

I tried activeCell.PivotCell.PivotTable.ActiveFilters and this returns 0 although there are definitely filters available.

The workaround which I'm not is very good I'm considering is:

activecell.PivotTable.PageFields.Items collection which also behaves weird:

activecell.PivotTable.PageFields.Count returns 2 at the same time it has 3 elements:
activecell.PivotTable.Item(0);activecell.PivotTable.Item(1);activecell.PivotTable.Item(2)

View 9 Replies View Related

Syncing Pivot Table Filters?

Mar 29, 2012

I have a pair of Pivot Tables with matching Pie Charts that look at Project Costs and Schedules. When I filter one, I'd like the other to filter automatically.

I found these links and used the code that Jerry supplied but I am running into trouble.

pivot table question

Pivot Table- "Master" Report Filter
My filters (PivotField names) include
Contractor
RCEs District
Year
Month

When I select a contractor from PivotTable1 it does not change PivotTable2.

There is additional code in this workbook that Jerry recently wrote concerning drilldown formatting. It was initially created for a different report but now I use it in many of my other reports. I don't think it's causing the problem, I placed the new code above it (including in the module) and there is a line breaking it up from the new code.

As a side note, I may or may not need to change every filter. In this case I only want to change the contractor, but other times I may want to change other items.

View 9 Replies View Related

Setting Up Column Filters In Excel?

Aug 14, 2012

setting up filters or sorting for different columns in excel.

I am trying to make a database for my research. At the moment my cells look like this (this is just and example, the real list would be much longer):

Source
Policy

IEA WEO
RE Policy Global

IEA Clean Technology Tracker
RE Policy China, US

[code]...

I would like to put a drop down menu that allows me to search my sources. For example, I want to find 'IEA' in the 'Source' column and only entries with 'IEA' in the title pop up. Or I want to find 'Wind' in the 'Policy' column and only entries with 'wind' come up.

View 2 Replies View Related

Looping Through Pivot Table Filters

Nov 18, 2013

Is there a way to loop through pivot table filters in VBA? In my case, I need to loop through the filter "Month" which contains January - December. For each of the month, I have to select and double click the total cases in each month.

So for every loop, for example,

Pivot 1
January
Count

Case 1
2

Case 2
1

Total
3

I need to open the drill down in B3 (this drill down will be opened in a new sheet, Sheet 1), copy the data and paste it in another sheet named "January Cases". Then the code will delete "Sheet 1" and proceed with the same with February till December. Is it possible to loop through pivot table filters?

View 1 Replies View Related

Macro Which Clears Filters In Pivot

Jun 20, 2014

I have a pivot table with various filters, and I would like to setup a macro assigned to a button which resets the filters to 'All'. Used this code to clear one field and it worked successfully:

Code:

Option Explicit
Sub ClearFilters()
'
' Clear Filters
ActiveSheet.PivotTables("FLIGHTS") _
.PivotFields("MONTH").ClearManualFilter
End Sub

The question that I have is: how can I list the other fields in the same macro, or is there a command that just resets all available field so they don't have to be listed individually?

View 4 Replies View Related

Pivot Filters Show Old Information

May 2, 2008

To minimize the amount of time I spend building reports each day I consistently reuse existing pivot tables for my daily reporting by deleting the data a pivot table is based on and inserting new data. When I refresh the pivot table the new information is shown.

However, I have noticed that the drop down filters in all my pivot tables are showing selections that existed in previous data but are not in the current set of data.

Is there a way to update the pivot so that the drop down filters only show selections that are pertinent to the current set of data?

View 9 Replies View Related

ADVERTISEMENT