Forum Discussion
Excel Vlookup or Indirect
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
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
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.