Forum Discussion
Excel Vlookup or Indirect
I did this as an example of what the Pivot Table could do very easily if you were to track all those overtime hours in a single database. Just add a column for the Pay Period. With a little more time, I could easily extract the detailed single pay period data as well.
It's a common mistake is to break things apart into monthly or weekly or (in this case) the 26 pay periods as the way to enter and store the data, when the fact is that Excel can do that breaking apart for period-specific reports very easily.
View that as an output rather than as a necessary way to enter and store the data. Excel is excellent at summarizing into reports from a single database. The Pivot Table is one of many tools that does that.
So in this one I just combined the first four Pay Periods and used the Pivot Table to summarize hours for each individual. If you were to add the data from more Pay Periods and then just hit "Refresh" under the Pivot table tool bar, you'd see how readily it accommodates that additional data.
thank you for taking the time to help me out Sirmathetes, i have a question. will that pivot table keep expanding if for lets say on tap PP 1 people kept adding times and other people that may work on that module for that day? cause i have formated each tab all the way down to row 2,000. dont think they will hit row 2000 but just in case they do will the pivot table track that?
When you spoke about purchasing Excel (without Power Query), did you purchase the actual spreadsheet layout here (is it a template that you've adapted/expanded to 26 pay periods)? What you have is quite sophisticated--that's why I ask.
As to your question about the Pivot Table expanding, the way I have done it in the past, so long as what you have is an actual table (which my "FullYrData" is) then, yes, all you need to do is click on the "Refresh" button on the "Pivot Table Analyze" tool bar.
If you were to add Employee ID# to your individual tabs, I'm sure that Power Pivot could also make the necessary joins, but if you don't have Power Query, you probably don't have Power Pivot.
Is it possible for your less sophisticated users to enter their data into a single table for the entire year?
A lot of random questions, for which I apologize.....I've modified the Pivot Table example so that the type of OT is broken out here, since that's part of what you wanted, I believe. Your "sample data" makes it look more symmetrical than I suspect the actual data would.
dont apologize i appreciate it the questions mathetes. im not the greatest at explaining these things hahaha. So i created this spreadsheet, its not a template and for whatever reason the excel version i have through work doesn't have the option for power query's. i have self taught myself how to use excel because i think it offers so much and the things it can do is endless. there is just some stuff (formulas) that i cant make. before i asked on here i tried to figure it out for awhile and couldnt haha.. i was pretty bummed when i couldn't use power query. i have heard awesome things its able to do and no i dont have access to power tables either. pivot tables yes but no power tables. my goal for this was to just type an employees name on the TOTALS tab and below it, auto populate the totals hrs worked for PLANNED and EXT STAFF for the year. i dont need it to show for each each Pay Period.
So i created this spreadsheet with just one tab so they were able to filter that data as such but after they used it they wanted tabs for different pay periods haha. i havent played with pivot tables all that much and the times i have messed around with them i couldnt make them work the way i wanted them too. just cant figure those out for some reason haha.
Thank you for taking the time to help me out Sir i really appreciate it
If possible i would love to just enter the employee name and under that auto populate all the PLANNED and EXT STAFF hrs for the year. i wont be using this sheet, people with less excel experience than me haha will be using this. so just trying to make it as simple as possible for them. So please keep the ideas coming im open to all ideas. thank you everyone for responding
