+ Reply to Thread
Results 1 to 10 of 10

Filter pivot table by cell value.

  1. #1
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    238

    Filter pivot table by cell value.

    I like to make a nice OEE dashboard of date from my work.
    For that i wan't chart and totals of 24 our only. So how do i filter several pivot tables to 24 ours only?

    Maybe to filter a table between two cell. So anyone can choose a begin date?

    Is there someone how can make a example. Maybe a nice dashboard to begin with? (totals, average/h, yield, etc)
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,176

    Re: Filter pivot table by cell value.

    Sometimes with pivot tables you need helper columns in the source data table. I created one called "In Date Range" and the formula for it is: =AND([@[Datum / tijd]]>=Pivot!$H$6,[@[Datum / tijd]]<=Pivot!$J$6)

    I refreshed the pivot table and used this as a report filter.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    238

    Re: Filter pivot table by cell value.

    This is working very nice!! Is there a youtube or manual how to do this. I have no idea what you did or how yo did it.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,176

    Re: Filter pivot table by cell value.

    No, I do not have a video, but I will eventually have an instruction set. One of my "jobs" at work is to teach other people Excel. I run a lunchtime lesson on a different thing about Excel once a month. Pivot tables will be on the January schedule.

  5. #5
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    103

    Re: Filter pivot table by cell value.

    Hi

    dflak just added a new column in your pivot data (sheet PKD). This column returns true or false, depending on the values you have below your slicer.
    Then filter the pivot table with this new true/false value.

    I was also working on a OEE Dashboard. It took me a while to grab data, transform and use the datamodel to create measures so the dashboard is automtically showing what i want to show.
    I used this board but also Youtube for some videos on how to make a dashboard, how the datamodel is working and how measures can be done.

    Overall, I guess you need to spend some time reading and learning

    Good luck

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,176

    Re: Filter pivot table by cell value.

    Thanks for jumping in and explaining. Helper columns are often the solution to difficult pivot table problems.

    For example, one of the problems with pivot table filters is that if you have everything selected and a new item appears in the data, then the new item also shows up checked as visible. However if you have ANY item checked off, then when a new item shows up, it appears in the filter checked as NOT visible. For example you may be analyzing failure reason codes for an item and you don't want "Other," so you check it off. Now a new reason code appears. It will NOT show up on the pivot table. In this case you should create a column called "Not Other" and the formula would be =[Reason] <> "Other".

    In general, you want these helper columns to evaluate to True / False, but I have used it to group failure reason codes into "Electrical," "Mechanical," etc. by using VLOOKUP against a table that groups them.

  7. #7
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    238

    Re: Filter pivot table by cell value.

    Thanks you two. I got it now! This wil make my live much easier. Is there a a way to see words in a chart? I like to see what the reason of downtime is in or below a production chart.

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,176

    Re: Filter pivot table by cell value.

    After you make the chart, right click on it. A plus sign (+) should appear just outside the cart on the upper right. When you click on this icon, you will get a menu of some of the things you can do with the chart. One of them should be data labels. Click on that and data labels will be added. Right click on any of the labels and you will get dialog boxes to format them.

  9. #9
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    238

    Re: Filter pivot table by cell value.

    Is there a way to count how many times a word is in the same time window?
    For example how many times the word "Dagelijkse taken" is in the column "reason downtime wasline" in the true time window

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,176

    Re: Filter pivot table by cell value.

    Look up the COUNTIFS function.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot table to filter by selected cell in table
    By pritesh118 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-10-2019, 05:03 PM
  2. Pivot table to filter by selected cell in table
    By pritesh118 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2019, 04:16 PM
  3. Set pivot table value filter with cell value
    By montefortej in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-10-2017, 12:50 PM
  4. Pivot Table to filter according to cell value
    By torrmel9701 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-08-2014, 04:15 PM
  5. Replies: 6
    Last Post: 07-31-2014, 12:56 PM
  6. Pivot table, assigning a cell wo be change the Pivot table filter
    By jwongsf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-28-2012, 05:00 PM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1