+ Reply to Thread
Results 1 to 6 of 6

Pivot Table and Slicer Help

  1. #1
    Registered User
    Join Date
    10-06-2014
    Location
    Cincinnati
    MS-Off Ver
    2013
    Posts
    56

    Pivot Table and Slicer Help

    Hello all,

    I am having troubling sorting and filtering a pivot table based on the way my data was laid out and I wanted to see if someone could assist. I have my data laid out with dates as headers to avoid using several thousands or rows for data. I have found this limits the way I can sort pivot tables and use slicers. If anyone has any suggestions I would greatly appreciate it!

    Attached is the workbook I am using.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: Pivot Table and Slicer Help

    Hi, this data is not normalized. A much better data model can be achieved by using listed dates. Explain what your intention for the workbook is to be, without going into technical details and I'm sure we can produce a solution. I'm guessing that you have an employee/ person list, scheduled hours and actual hours. Is this the case? If so, are the scheduled hours static, i.e. Joe works 3 hours per day or are they varied, i.e. Joe will work 3 hours monday, 4 tuesday, 6 next Monday and so on? How is the data to be entered? Is it a manual process or are you importing / pasting lists from another source?
    Frob first, tweak later

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

    Re: Pivot Table and Slicer Help

    Unfortunately, pivot tables work best with the Excel version of "normalized" data. In this case 4 columns of Name, Badge Number, Status and Date.

    You may have more rows of data, but you actually have less data. All those blank cells go away.

    It looks like you have this set up on the TOD Hours Sheet. You may need a helper column to lookup the scheduled and compute the differences.
    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.

  4. #4
    Registered User
    Join Date
    10-06-2014
    Location
    Cincinnati
    MS-Off Ver
    2013
    Posts
    56

    Re: Pivot Table and Slicer Help

    Thanks for the reply! My data is not static and the schedules change regularly. What I have going on is that I past data into the schedules tab and then the schedules table is looking up this information to populate. I do the same with the actual hours which is labeled TOD hours and table. I then calculate the differences on a separate table. This is where I created the pivot table on the summary page.

    Do you have any suggestions on how I could manipulate this data?

  5. #5
    Registered User
    Join Date
    10-06-2014
    Location
    Cincinnati
    MS-Off Ver
    2013
    Posts
    56

    Re: Pivot Table and Slicer Help

    That is exactly what is happening here. Do you have any suggestions as to how I could manipulate this data to create a running total for the helper which includes all data? My problem I guess is that I cannot figure out how to have the names populate every time there is a new date/names added. There will sometimes be data for the actual and nothing for the scheduled, the same applies vice versa.

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

    Re: Pivot Table and Slicer Help

    I make use of Excel Tables. See this article on use of tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

    The two key tables are TOD Hours and Schedules. I put these two tables on the same sheet called Data. I consolidated the personal info into a table Called Table_Names. I do data validation of the names against this table. The "cheap and quick" way to validate against a column in a table is like so:
    =INDIRECT("Table_Names[Name]") - as you add or delete names, the validation will keep up.

    The tables use VLOOKUP to get badge number and Status from the name. In the TOD Table, I use the formula =SUMIFS(Table_Schedules[Duration],Table_Schedules[Start Date],[@Date],Table_Schedules[Full Name],[@[TOD Name]]) to get the scheduled hours from the scheduled table and the formula =[@Hours]-[@Scheduled] to compute the difference.

    I use the TOD Table as the data source for the pivot table on the Summary sheet.
    Attached Files Attached Files

+ 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 and slicer help
    By finchfinch in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 06-23-2016, 07:36 AM
  2. Replies: 19
    Last Post: 04-04-2016, 02:58 PM
  3. [SOLVED] Pivot Table Slicer function
    By ChemistB in forum Excel General
    Replies: 3
    Last Post: 09-28-2015, 09:09 AM
  4. Pivot Table Slicer Problem
    By skate1991 in forum Excel General
    Replies: 2
    Last Post: 02-28-2014, 09:54 AM
  5. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM
  6. Pivot Table (Slicer) Help
    By skate1991 in forum Excel General
    Replies: 0
    Last Post: 10-11-2012, 11:52 AM
  7. Pivot Table Slicer Limit?
    By zlehmann in forum Excel General
    Replies: 0
    Last Post: 11-21-2011, 12:35 PM

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