Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Am I thinking right... (XL97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    OK I am trying to produce a schedule and what I am hoping for is this:

    I have a list of employees. I also have a list of positions. These positions should be manned 24 hours a day 7 days a week. So what I did is:

    I put all the positions in column A. I have Sunday to Saturday in Columns B through H. In column W I have the names of the people who can handle the jobs.

    Now I use Data Validation drop-downs in all of these cells in the range B2:H25 that looks at that list of names in column W. One problem shows up...

    Some times a mistake is made and a name is inserted in two positions, which is impossible because you can't be in two placed at the same time. So...

    How do limit the name to appears in only one cell in the B2:B25 range. Could I eliminate the name from subsequent drop downs in the same column?

    Thanks for any advice.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Am I thinking right... (XL97 and >)

    Reducing the dropdown lists would be a lot of work.

    Since you already use Validation with the List option, you can't use Validation to forbid duplicate entries. You could use conditional formatting to flag duplicates:
    - Select B2:H25. B2 should be the active cell within the selection.
    - Select Format | Conditional Formatting...
    - Select Formula Is from the first dropdown.
    - Enter the following formula in the box next to it:
    <code>
    =COUNTIF(B$2:B$25,B2)>1
    </code>
    - Click Format...
    - Activate the Pattern tab.
    - Select red as background color.
    - Click OK to close the Format Cells dialog.
    - Click OK to close the Conditional Formatting dialog.

Posting Permissions

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