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

    Unlock cell on condition (2003) (2003)

    I have a worksheet with employee information that I am preparing for an annual merit pay increase process. Each row contains information for one employee job. Employees may have many jobs. Some of these jobs are exempt from merit increase. I have a list of exempt job codes in column C (approximately 60 values). Column M contains the employees job code for that row. I would like to only unlock the cells in column X for rows that DO NOT contain values within the exempt list in column C, and if the value is listed in column C put the text "EXEMPT" in the cell.

    I appreciate any assistance that can be given.
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlock cell on condition (2003) (2003)

    Assuming the exempt list is in the range C1:C60.
    Assuming Cell A1 contains the job that may be found in Exempt list
    Assuming you are in cell B1

    Choose Data, Validation
    Select custom from the first dropdown and paste this formula in the bottom box:

    =OR(ISNA(MATCH(A1,$C$1:$C$60,0)),B1="EXEMPT")

    Once cell A1 contains a job that is in the exempt list, the cell B1 will refuse anything else than exempt. See attached.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlock cell on condition (2003) (2003)

    If Jan Karel's solution is not good enough (it doesn't really lock the cells), then you can use VBA code in the Worksheet Change event routine to really lock the cells as you describe. If you want to do that, could upload an example worksheet that could be used for testing the code with any proprietary info changed.
    Legare Coleman

Posting Permissions

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