Excel Vlookup or Indirect

[spalmer]

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.

[#2]

@spalmer , I went with a Power Query solution in case you need to add more pay period sheets. This will work as long as the new pay period data are in tables. It just excludes the Roster Table. 

[#3]

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

[#4]

@spalmer 

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.

[#5]

thank you for taking the time to help me out Sir@mathetes, 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?

[#6]

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 

[#7]

@spalmer 

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.

[#8]

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

[#9]

@spalmer 

So a few responses to your points:

You wrote: 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.

To which I respond that what you've created is quite sophisticated. Nothing to be ashamed of.

You've committed (in my opinion) the mistake of making it a little more glittery (fancy) before making it fully functional. I'd recommend working on functionality (to include knowing the functions and formulas) and adding nice appearances later.

You wrote: 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

To which I respond: Leave the different tabs for the Output end of things. Excel is excellent at collecting data in a single table and then offering (through such tools as the Pivot Table, but also now--with the newest release's Dynamic Array Functions, FILTER, UNIQUE, SORT...) different ways to extract that data in summary form. So a per-pay-period output or summary is easily produced, as is an overall total. Work to differentiate Input (the database end) from Output (the summary, sliced, parsed....) reports. Resist the temptation (which naive users will often begin with) to use an output or summary format as the basis for collecting and organizing data.

You wrote: 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.

To which I respond: Pivot Tables have been, for decades, among the most popular and (frankly) easy ways to produce the kind of summary report you need. They can be filtered to show only one individual, or all, as the attached revision to my earlier posts shows.

I highly recommend you spend just a little time on YouTube's many instructive videos. Here, for example: https://www.youtube.com/watch?v=m0wI61ahfLc

Or some of the resources here: https://exceljet.net/lessons/what-is-a-pivot-table

[#10]

I really appreciate the ideas Sir @mathetes, im always looking for more ways to get better at creating spreadsheets so thank you for the ideas.  i will re do this spreadsheet to make it more simplistic.  the only crappy thing is i also dont have access to the dynamic array functions- FILTER, UNIQUE and SORT.  unless there is something i need to add in or something but cant access those.

[#11]

@spalmer 

"Simple" and "clear" aren't the same as "simplistic." Please don't confuse them.

those functions are in the newest subscription version of Excel, Microsoft 365 Subscription. I have the Mac Version. You can be sure that the Windows system has it as well.

[#12]

Copy@mathetes

i dont have it.  when i try to type in the function it doesnt show up on the list of formulas to choose from.

[#13]

is there a way to activate them?

[#14]

@spalmer 

As I said, it's only on the most recent release. So you need to upgrade your edition of Excel, most likely get a subscription to Microsoft 365.