Forum Discussion
Excel Vlookup or Indirect
hey everyone, hope all is well.
i have built a spreadsheet using slicers to track Overtime through out the year. in my spreadsheet i have 26 tabs for 26 pay periods to track 2 different types of overtime "PLANNED" and "EXT. STAFF." i created a TOTALS tab to calculate all the overtime each module worked and to see what each individual worked. in the totals tab i created a search option so i can search a name. below it, i want it to gather the total hrs worked from each Pay Period "PP" PLANNED and EXT STAFF and then total it for the year for that individual. i couldn't figure out the formula to do that so any help would be great. i attached the spreadsheet as well and hopefully it makes more sense what im trying to explain.
Thank you in advance to any and all who take the time to help me.
13 Replies
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.
- So not sure if this will matter but my company that bought the excel doesnt have power query. I tried using that and it doesn't show up when inputting it. Is there another way beside power query
