Excel Check Employee Rates

Megsnd

New Member
Joined
Oct 21, 2019
Messages
2
Hello! I have a spreadsheet I use for creating invoices for our clients. We have formulas that calculate the raw rate (showing employees rate before any profit multipliers are applied) for each line. This is part of our checks and balances process for our client, as they look at the rates for every single line and make sure it matches what they expected the rate to be (historical rates or contracted rates). Problem is, there are two instances where we need to identify issues with the rates before we send the invoice to the client, and up to this point I have been doing it manually...which with large invoices can take hours to go through line by line and try to find these issues. Issue number 1, the rate is sometimes off by 1 penny if the employee only charges a half hour. This doesn't change our total billed amount and is just a simple rounding issue, but our client will reject the invoice if anyone's rate is off even a cent. Issue 2, our accounting system automatically distributes an employee's overtime across all the projects they have worked that week...but this particular client will NOT pay for our overtime. So each month I look through every line on the invoice to see if their rate is inflated, and if I find an instance that their rate differs from their typical rate (and its not because of the rounding issue noted above) we have to go back into our system and recreate our invoice form by overriding their labor rate. As you can imagine, this is very time consuming.

So here is what I am looking to do:
I want to create a list (on each individual spreadsheet, or on a master list on a separate spreadsheet) that lists each employee's name and the rate they currently charge out at. Then I want Excel to match the name and rate from the list, to the name and rate on the invoice section. If the names match but the rates don't, I would like it to somehow show me which specific cell has the issue. I was thinking the best way would be conditional formatting where the cell will turn red if its not the same rate as the master list, but I don't know how to do that. I've tried different combinations of IF, VLookup, Match, etc...
I do not want to change how the rates are calculated, as this form and the formulas came directly from our client and we are not to change how they function. I simply want to automate a way to show me which rates don't match the listed rate for that employee.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,143,623
Messages
5,719,805
Members
422,245
Latest member
Kebad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top