Hi guys/gals, was wondering if someone could help me solve my dilemma.
I have a bonus and timesheet I use for my employees that gets transmitted to the timekeeper for entry into payroll and time systems. The bonus for each employee depends on a number of factors...pay grade, whether work is onshore or offshore, and whether work is perfomed on a holiday (doubles their regular bonus). There are 2 additional bonus categories for Operations coordinator and technical trainer that any of the pay grades can also perform that are flat rate.
So my table would look like this:
Pay Grade, Onshore, Offshore, Ops Coord, Tech Trainer
Field Spec III, 160, 220, 60, 60
Project Coord, 185, 245, 60, 60
Field Eng Trainee, 95, 155, 60, 60
And the list goes on (There are 15 different pay grades). I have employees type their name into the master tab, and pick their pay grade from a drop down. Then each 2 week pay period they enter free text for the contract number, choose the bonus code from a dropdown (onshore, offshore, ops coord, tech trainer) which then looks up the appropriate bonus code. I'm using the following array:
=IFERROR(INDEX(BonusValue,MATCH(1,($G$8=Title_Lookup)*(E21=BonusLookup),0)), 0) +ctrl+shift+enter
For holidays, I simply went to the 11 holidays for the year and multiplied the above by 2.
This works beautifully in 2007 and newer. I sent it out forgetting that I still have a bunch of guys with old laptops and the complaints quickly starting coming in that all they saw was #n/a. Can anyone help me figure out a way to perform this that works across all versions of excel???
Bookmarks