Hi
I have a spreadsheet that comes every month with a lot of payroll data on it. I need to update a master sheet that holds summary details each month.
My problem is that the data in the columns from the payroll spreadsheet can be different each month eg month 1 col D may have OT1 but next month because no one has had OT1 col D may be hol pay, now 0t1 and ot2 are combined in the summary but holiday pay is added to salary,which can also move on the payroll spreadsheet, in the summary.
my question is, is there a way to pull the info into the correct summary column on the master sheet every month. Currently use a vlookup array but have to edit it each month to include the correct columns from the payroll spreadsheet.
The column headings are always the same just in different places on the payroll spreadsheet. eg
month 1
col D has ot1,col E has ot2, col F has Hol Pay, Col G has Bonus, Col H has Salary
month 2
Col D has Hol Pay col E has Sick Pay Col F has Bonus Col H has Redundancy Col I has Salary
month 3
Col D has ot2 Col E has Hol Pay Col F has Salary
I need on my master sheet to combine ot1 and ot2 in Column C , I need Hol pay , Bonus and Salary summed in column G, Sick Pay in Column D and Redundancy in column H.
Hope this is clear
Thanks
Rick
I have a spreadsheet that comes every month with a lot of payroll data on it. I need to update a master sheet that holds summary details each month.
My problem is that the data in the columns from the payroll spreadsheet can be different each month eg month 1 col D may have OT1 but next month because no one has had OT1 col D may be hol pay, now 0t1 and ot2 are combined in the summary but holiday pay is added to salary,which can also move on the payroll spreadsheet, in the summary.
my question is, is there a way to pull the info into the correct summary column on the master sheet every month. Currently use a vlookup array but have to edit it each month to include the correct columns from the payroll spreadsheet.
The column headings are always the same just in different places on the payroll spreadsheet. eg
month 1
col D has ot1,col E has ot2, col F has Hol Pay, Col G has Bonus, Col H has Salary
month 2
Col D has Hol Pay col E has Sick Pay Col F has Bonus Col H has Redundancy Col I has Salary
month 3
Col D has ot2 Col E has Hol Pay Col F has Salary
I need on my master sheet to combine ot1 and ot2 in Column C , I need Hol pay , Bonus and Salary summed in column G, Sick Pay in Column D and Redundancy in column H.
Hope this is clear
Thanks
Rick