Results 1 to 4 of 4

Thread: Lookups (2003)

  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookups (2003)

    I am trying to set up a spreadsheet to tabulate the results of a merit scoring period for a group of employees. Each supervisor returns a spreadsheet to me with several columns of data including employee name, job code and the merit score. Depending on the job code the score would be applied to one of seven different tables to define the category and wage increase. The tables are divided into three sections one each for Exceeds, Meets or Below Expectations, and the score is compared to the range for each scoring level. I have been able to set up a lookup to identify which table the job should be scored from, but my challenge has been to use that result to define the table which would then define their wage increase and category.

    I have attached a stripped down spreadsheet that contains the spreadsheet the supervisors return on one tab, and the other tab contains the lookup for the table, and the tables themselves.

    I tried nesting IF statements, but I know there is a limit of 7 deep and this table of scores may expand in the future as more jobs are added. A VBA solution is OK as I am beginning to learn some coding.

    Thanks in advance for any assistance that can be provided.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookups (2003)

    Can you provide a step by step explanation of how the result should be calculated for, say, the employee in row 14 of the Aquatics sheet?

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups (2003)

    Hans,

    Step-by-step, the worksheet needs to look at the job code and determine which merit plan that job belongs to. Then it needs to use that merit plan and determine the employees rating and wage increase based on the score assigned by the supervisor.

    It is amazing what taking your 5th break from a project can do. I found an error in one of the named ranges, and then realized that I had inverted the lookup table so my IF/VLOOKUP statements could not work. The spreadsheet now works the way I expected it to.

    I am still challenged by the limitations of the method I am using (nesting IF statements). If my table of max scores increases by even one category my worksheet will fail. Is there a better way to accomplish what I have done here? I have attached the working spreadsheet for you to see.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookups (2003)

    Ah yes, the order of the lookup table was one of the things confusing me.

    See the attached version. I have inserted an intermediate column; it can be hidden. The formulas now use MATCH, OFFSET and INDEX.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •