Forum Discussion
adding data from worksheets into a master worksheet
jjohnson987 Not sure I follow. You mention that you "bring in totals from different tabs". Do you use a link? Or do you copy something manually? I imagine your ultimate goal is to gather all time records for all workers/employees into one data base, and I presume you don't want ot just copy/paste the daily records once they have been checked.
Could you upload an example of the file (delete any private information) you are working with?
Riny_van_Eekelenthank you for your help. Attached is the workbook I am working in. I have added the tab For Payroll Purpose and I am trying to get the employee IDs to the main tab then with vlookup bring in the daily time to the main tab for employees total time for the week. Then Payroll could copy and paste the weekly totals into our payroll software to pay out.
jjohnson987 Your VLOOKUP formulae are correct, though I changed one thing small thing. The IFERROR returns "0" if the ID was not found. I assume you want it to be the number -zero-, not a zero as a text.
There are no matching records in you daily tabs compared with column A in the Master sheet. To test your formula, I inserted an employee ID from the Monday sheet in row 3 on the Master. (See attached). I believe that it returns the correct result. Please make sure that the ID's for all employees in the daily tabs or in your master list. Let me know if I missed something.
Riny_van_EekelenGood morning,
That is where the problem lies, how do I bring in all the employee ID's that are on the daily tabs? Duplicate employees could be on the tabs as we could have the same employee all week working. I was using the formula =Monday!D10:D47 in cell A1 and that will work for Monday but I want to be able to bring in the other employees that are on the additional day tabs to column A and then do a Vlookup to bring in the time for each day tab to columns B-H with a total hours in column I for payroll purpose. I understand once I bring in all the EE's from the remaining tabs to column A there will be duplicates and I was ging to use conditioning formatting to find the duplicates so the Vlookup will work properly.
Once all the EE data is cleaned up in Column A, our Vlookup should work for the columns B-H and then Payroll will then take the data in column I and copy/paste into a new workbook for an import into our payroll system.
I have attached the most recent example.
Thank you for all your help and patience.
jjohnson987 I apologise for being slow of mind. But Payroll should know who is employed and should thus be able to create the list of all current employees with their unique ID's. Otherwise, they wouldn't be working for you in the first place, I hope.
That's your master list and with it, you can do the VLOOKUP as you have it and pull the data from the daily lists into the master list. But perhaps I'm just not understanding your problem.
You can make things easy with an INDIRECT with this setup, but before I dive in my concern is will new tabs be added eventually for new weeks?
- no, the spreadsheet will remain with the 7 day tabs. It is basically a weekly timesheet for our team leads to capture time on our day labor, daily. We will eventually be moving to an electronic time keeping system. In the next few months this spreadsheet will be obsolete.
