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.
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
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.
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?
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks