Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers....I have a rather big spreadsheet that I use for yearly scheduling of a couple of dozen people..a year at a time, into 24 work sites daily.....I use drop-down menus with validation formulae to enable me to choose people (and not double-schedule someone).......I attach a very small sample of the scheduling worksheet that I have assembled, in order to illustrate my question/inquiry.

    All staff are shown as "Available" when I do the schedule at the start of the year (and I assign them a Job somewhere in C6:G13). The range C6:G13 have a data validation formula in each cell that displays the names shown further along the same row in ranges M6:Q13 -- this permits me to pick a name from the drop-down list and assign that person when doing the assignments in the first instance and b/c their name disappears once assigned, they won't be double-assigned. However, if the person's assignment folds after being assigned, the person is then put under "To be assigned" and their name shows in H6:J13..... but, if they are in range H:J, before I can assign them, I have to remove them from H:J so that they re-appear in M:Q in order to have them show in the drop-down menu that I pick from ......... I am looking for a validation formula in C:G (eg: =H6:J6+M6:Q6...??) that would 'read' both the H:J rows and the M:Q rows, so that I could pick anyone and it would remove their name from the 'To be assigned' or the 'Available' lists, no matter what 'category' they were in....
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel does not allow the union of discontiguous ranges to be used for list-based validation. You will have to place the "To be assigned" and "Available staff" ranges next to each other, so that you can refer to them as one contiguous range.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you for that, Hans......I (sort of) knew that but was hoping that there would be some kind of work-around........ever the optomist...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps I am obtuse, but what is the purpose of the "To be Assigned" location?

    If you delete them from the job list and do not have the TBA, they should show up in the Available staff automatticaly and thus it only requires the "available staff".

    It seems to me the TBA are "available staff" and should just be treated as such...


    Steve

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    You are right, Steve...I use the TBA b/c the data from this worksheet copies onto a different sheet that is posted online...usually, there are only about 3 or 4 people who become TBA and so I only need to provide for 3 or 4 rows ..... if I had to accommodate 24 people, and many of the rows were blank b/c the original assignments did not change, then the online sheet would be unecessarily big and mostly blank......what might work would be formula in the TBA cells that automatically draw in, say in alphabetical order, any names from "Available" that aren't assigned -- and as I assign the names, other unassigned names move into the roster of cells under TBA...but that would likely be a very complex formula.....??...???

Posting Permissions

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