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.
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.
