Hello!
I am trying to automate a General Ledger worksheet. I have created three worksheets. The first is "Payroll Data." This sheet is dynamic and could change each week.
SAMPLE PAYROLL HERE:
The second sheet is "PROFILES." This sheet contains all the employee numbers, names, and the triggers that indicate the department they get posted too. For example: Employee 010267 has a one (1) under 4080 FABRICATION. This trigger indicates two things: 1. That this person is posted in fabrication, department 1 and this is the multiplier that their salary is divisible by. The reason for this is some people are broken up over all 16 departments so in all the boxes I put a "15" in each so the salary is divided into each. This was the only way I though of that I could build a profile and just tweek this profile sheet to make any changes.
SAMPLE PROFILE HERE:
The third sheet is the "GL-Template" sheet. Starting to the left. You will see each employee number that makes up this department. Next is the GL Account # and then GL Description. Then the debits and credits.
I want to be able to put a formula in the respective debit or credit field that will read all the employees to the left, even if its blank it should sum it up. Some departments have 15 employees, some have 1. I want to formula to be consistent so if an employee is added to the left it automatically looks up the payroll information and does the calculations. An example of what I have done is in "S21". You will see the 1017.50. Which is the gross total of 110013 and 110015, but if I add a third employee or I remove the second. I will get a blank cell.
Here is the formula I used for the 1017.50: =IFERROR((VLOOKUP(A21,'PAYROLL DATA'!$A$4:$AS$162,4,0)/VLOOKUP(A21,PROFILES!$A$6:$TJ$168,33,0))+(VLOOKUP(B21,'PAYROLL DATA'!$A$4:$AS$162,4,0)/VLOOKUP(A21,PROFILES!$A$6:$TJ$168,33,0)),"")
(A little sloppy I think, but I am learning -
)
SAMPLE GL-TEMPLATE HERE:
Again, I also need it to pick up the "trigger" in the Profiles so that it will divide the number, if necessary.
Any questions or assistance is greatly appreciated!
Jack
I am trying to automate a General Ledger worksheet. I have created three worksheets. The first is "Payroll Data." This sheet is dynamic and could change each week.
SAMPLE PAYROLL HERE:
The second sheet is "PROFILES." This sheet contains all the employee numbers, names, and the triggers that indicate the department they get posted too. For example: Employee 010267 has a one (1) under 4080 FABRICATION. This trigger indicates two things: 1. That this person is posted in fabrication, department 1 and this is the multiplier that their salary is divisible by. The reason for this is some people are broken up over all 16 departments so in all the boxes I put a "15" in each so the salary is divided into each. This was the only way I though of that I could build a profile and just tweek this profile sheet to make any changes.
SAMPLE PROFILE HERE:
The third sheet is the "GL-Template" sheet. Starting to the left. You will see each employee number that makes up this department. Next is the GL Account # and then GL Description. Then the debits and credits.
I want to be able to put a formula in the respective debit or credit field that will read all the employees to the left, even if its blank it should sum it up. Some departments have 15 employees, some have 1. I want to formula to be consistent so if an employee is added to the left it automatically looks up the payroll information and does the calculations. An example of what I have done is in "S21". You will see the 1017.50. Which is the gross total of 110013 and 110015, but if I add a third employee or I remove the second. I will get a blank cell.
Here is the formula I used for the 1017.50: =IFERROR((VLOOKUP(A21,'PAYROLL DATA'!$A$4:$AS$162,4,0)/VLOOKUP(A21,PROFILES!$A$6:$TJ$168,33,0))+(VLOOKUP(B21,'PAYROLL DATA'!$A$4:$AS$162,4,0)/VLOOKUP(A21,PROFILES!$A$6:$TJ$168,33,0)),"")
(A little sloppy I think, but I am learning -
)SAMPLE GL-TEMPLATE HERE:
Again, I also need it to pick up the "trigger" in the Profiles so that it will divide the number, if necessary.
Any questions or assistance is greatly appreciated!
Jack
Last edited:
