On sheet 1(sales) In column B there will be hundreds of invoice numbers(no duplicate invoice numbers because each invoice number represents a sale with the associated customer name, sale price, etc. Column E is sales price, F is type of sale, G is max percentage that should be paid on that type of job, H is how much has currently been input as payroll from sheet 2 (payroll).
On sheet two (payroll) in column B there will be invoice numbers and many will be duplicated because multiple people will be going to the same job to do the install and have pay associated with that job. In column H it will have how much has been paid to the various employees for installing that job.
(spreadsheet link) excel - Google Drive
What I'm trying to do is have sheet 1 check against all invoice numbers from sheet 1(sales) column B against their matching invoice numbers on sheet 2 (payroll) column B then when it finds all the matching invoice numbers counts up the totals in column H on sheet 2 and sums them into sheet 1 column H.
The goal is to have a way to track how much employees have charged to each job and make sure it does not go over the max percentage the company can afford to pay on that job. When a job takes several weeks and multiple people may be going to that job and claiming pay on their weekly payroll it because an impossible task to keep up with every job and every payroll to make sure theft or error isnt taking place.
In addition to that I created a third sheet (calculator) which gives a max percentage for every sales category which are then used on sheets 1 and 2. Once I can get the intial formula figured out I'm wanting excel to cross reference columns H against the calculated and if the percentage exceeds the max percentage allowed then the percentage in H turns red to make it easy to spot overpaid jobs.
I'm not even sure if all of this is possible, but I'm hoping someone knows how to do this with excel 2013l
On sheet two (payroll) in column B there will be invoice numbers and many will be duplicated because multiple people will be going to the same job to do the install and have pay associated with that job. In column H it will have how much has been paid to the various employees for installing that job.
(spreadsheet link) excel - Google Drive
What I'm trying to do is have sheet 1 check against all invoice numbers from sheet 1(sales) column B against their matching invoice numbers on sheet 2 (payroll) column B then when it finds all the matching invoice numbers counts up the totals in column H on sheet 2 and sums them into sheet 1 column H.
The goal is to have a way to track how much employees have charged to each job and make sure it does not go over the max percentage the company can afford to pay on that job. When a job takes several weeks and multiple people may be going to that job and claiming pay on their weekly payroll it because an impossible task to keep up with every job and every payroll to make sure theft or error isnt taking place.
In addition to that I created a third sheet (calculator) which gives a max percentage for every sales category which are then used on sheets 1 and 2. Once I can get the intial formula figured out I'm wanting excel to cross reference columns H against the calculated and if the percentage exceeds the max percentage allowed then the percentage in H turns red to make it easy to spot overpaid jobs.
I'm not even sure if all of this is possible, but I'm hoping someone knows how to do this with excel 2013l
