March 20, 2019
I have a workbook with 2 sheets and all I want to do is to be able to download the feed sheet and paste it in to update the front sheet.
The 2 sheets are named Payroll (front sheet) and Hours (feed sheet).
Once the feed sheet has been pasted in I need it to update columns 0:AB on the front sheet (Payroll)with the number of hours and absence type from the feed sheet (Hours), this will also need to cross reference the specific absence code (located on front sheet columns AF:AG).
I have updated the 1st row 'Tom Smith' manually with what the data would look like if this can be done?
Regrettably, I am unable to amend any formatting as the workbook is provided to us by our payroll team and has to stay in that format.
If anyone out there can help me with this I would be very grateful and I hope I have explained it well enough.
Thanks in advance,
March 21, 2019
March 20, 2019
December 7, 2016
Just took a quick glimpse of your sample file. If you are able to get the hours data in a tabular format + in that data also have the employee number then it would be much easier for you to fill the Payroll sheet. But without help of VBA code I find it difficult, if not impossible, to get a dynamic approach. Perhaps doable if using a Pivot Table in between. A lot of ifs here, but I do hope you find a way through.
It depends on what Excel version you have if you have Power Query or not.
June 25, 2016
I agree with what Anders said. Your data is pretty messy with a lot of merged cells and need to be "cleaned".
You mentioned that the format in your workbook cannot be amended and this is very tricky to handle.
The Hour sheet have 2 unused columns (A and B) and the data is structured enough for formulas to be used to extract the required info.
You can also make use of other columns to the right of your data if you want but then you will need to use INDEX and MATCH (instead of VLOOKUP) to extract the info.
I have created some formulas (it is very complex, maybe there is a better way but I can't think of one) for me to then do a VLOOKUP from the Payroll sheet.
Hope this helps.