Log In
Register
Home
Active Member
March 20, 2019
Offline
Hi everyone,
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,
Tom
March 21, 2019
Offline
Hello Tom,
I have not reviewed your sample file. Still one question did you try the Merge function of Power Query?
If not please, I am sure it should solve your problem.
Regards
Santosh
Active Member
March 20, 2019
Offline
Hi Santosh,
Thanks for your suggestion, I have never used Power Query and when I have tried to add it, the only options I have are for Power View, Power Map and Power Pivot.
Tried googling it and followed the instructions and I cannot find it?
Regards
Tom
VIP
Trusted Members
December 7, 2016
Offline
Hello Tom,
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.
https://support.office.com/en-.....F6269CD605
Br,
Anders
VIP
Trusted Members
June 25, 2016
Offline
Hi Tom
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.
Good luck.
Sunny
1 Guest(s)
