Formula in dropdown list

aestable

New Member
Joined
Apr 19, 2016
Messages
4
Hello,

I am trying to create a drop-down list based on a (rather complicated) formula, and since the validation rule won't accept a formula, I'm not sure how to proceed. Essentially, I am trying to create a spreadsheet for scheduling staff in various locations, based on their qualifications and availability.

So I have three worksheets:
1) A list of staff members with the areas in which they are qualified to work, e.g.:
Area 1Area 2Area 3
ArchieXX
BarbX
CharlieXXX
DaveXX
EricXX

<tbody>
</tbody>











2) The same list of staff members, with the days of the month on which they are available, e.g.:
Sun 1Mon 2Tue 3Wed 4Thu 5Fri 6
ArchieYYYYYN
BarbNNNNYY
CharlieYNYNYN
DaveNYNYNY
EricYNYNNY

<tbody>
</tbody>











3) A calendar with the different areas, which I have to fill with the names of the staff:
Sun 1Mon 2Tue 3Wed 4Thu 5Fri 6
Area 1ArchieArchieCharlieetc
Area 2CharlieArchie
Area 3EricDaveEric

<tbody>
</tbody>








I am at the point where I do conditional formatting which turns the background red if I enter the name of someone who is unavailable that day. But I would like to have in each cell a drop-down list with the names of the staff who are qualified to work in that area AND who are available that day. Probably a combination of INDEX MATCH and INDEX SMALL ROW, but I'm not quite at that stage yet.

I managed to pull up the list of people by qualification with an array formula that creates a list in a separate cell range, which I define by name and call up in the drop-down, but it's a bit ugly. Can anybody make a suggestion? I'm considering doing that using SQL and database tables, but I would have to develop the entire interface and I'm not quite ready for that - yet...

Thanks in advance for any advice!

Regards
Axel
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
634
Hello,

I am trying to create a drop-down list based on a (rather complicated) formula, and since the validation rule won't accept a formula, I'm not sure how to proceed. Essentially, I am trying to create a spreadsheet for scheduling staff in various locations, based on their qualifications and availability.

So I have three worksheets:
1) A list of staff members with the areas in which they are qualified to work, e.g.:
Area 1Area 2Area 3
ArchieXX
BarbX
CharlieXXX
DaveXX
EricXX

<tbody>
</tbody>











2) The same list of staff members, with the days of the month on which they are available, e.g.:
Sun 1Mon 2Tue 3Wed 4Thu 5Fri 6
ArchieYYYYYN
BarbNNNNYY
CharlieYNYNYN
DaveNYNYNY
EricYNYNNY

<tbody>
</tbody>











3) A calendar with the different areas, which I have to fill with the names of the staff:
Sun 1Mon 2Tue 3Wed 4Thu 5Fri 6
Area 1ArchieArchieCharlieetc
Area 2CharlieArchie
Area 3EricDaveEric

<tbody>
</tbody>








I am at the point where I do conditional formatting which turns the background red if I enter the name of someone who is unavailable that day. But I would like to have in each cell a drop-down list with the names of the staff who are qualified to work in that area AND who are available that day. Probably a combination of INDEX MATCH and INDEX SMALL ROW, but I'm not quite at that stage yet.

I managed to pull up the list of people by qualification with an array formula that creates a list in a separate cell range, which I define by name and call up in the drop-down, but it's a bit ugly. Can anybody make a suggestion? I'm considering doing that using SQL and database tables, but I would have to develop the entire interface and I'm not quite ready for that - yet...

Thanks in advance for any advice!

Regards
Axel


aestable,
I don't know if you are willing to try VBA…but here is some code that will accomplish your stated goal.
You did not say where the charts were on the individual sheets…so I put all three ranges on the same sheet to work out the logic for my code.
Sheet1 - Range("B2:E7")
Sheet2 - Range ("G2:M7")
Sheet3 - Range("O2:U5") Output

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Sun 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Mon 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Tue 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Wed 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Thu 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Fri 6</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Dave</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Dave</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Dave</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie Eric</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie Eric</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Archie Charlie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Eric</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Charlie Eric</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Dave</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Charlie Eric</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Dave</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Barb Charlie</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Barb Dave Eric</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Names available for each DAY and AREA are all listed in that cell. You can select one and clear the others from each cell.
Not a drop down list, but it does "...create a spreadsheet for scheduling staff in various locations, based on their qualifications and availability."
You should put the following code in a standard module (Alt F11, then paste the code in the large window, then close the window and save the file as macro-enabled.)
To run the code first put your info in the ranges I have indicated. Then press Alt+F8, then select 'WhoCanWork', the press 'Run'.
Perpa

Code:
Sub WhoCanWork()
Dim rw, n As Integer
        For rw = 3 To 7
            'Sunday
            If Cells(rw, "H") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "P") = Application.Trim(Cells(3, "P") & " " & Cells(rw, 2))
            If Cells(rw, "H") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "P") = Application.Trim(Cells(4, "P") & " " & Cells(rw, 2))
            If Cells(rw, "H") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "P") = Application.Trim(Cells(5, "P") & " " & Cells(rw, 2))
            'Monday
            If Cells(rw, "I") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "Q") = Application.Trim(Cells(3, "Q") & " " & Cells(rw, 2))
            If Cells(rw, "I") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "Q") = Application.Trim(Cells(4, "Q") & " " & Cells(rw, 2))
            If Cells(rw, "I") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "Q") = Application.Trim(Cells(5, "Q") & " " & Cells(rw, 2))
            'Tuesday
            If Cells(rw, "J") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "R") = Application.Trim(Cells(3, "R") & " " & Cells(rw, 2))
            If Cells(rw, "J") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "R") = Application.Trim(Cells(4, "R") & " " & Cells(rw, 2))
            If Cells(rw, "J") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "R") = Application.Trim(Cells(5, "R") & " " & Cells(rw, 2))
            'Wednesday
            If Cells(rw, "K") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "S") = Application.Trim(Cells(3, "S") & " " & Cells(rw, 2))
            If Cells(rw, "K") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "S") = Application.Trim(Cells(4, "S") & " " & Cells(rw, 2))
            If Cells(rw, "K") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "S") = Application.Trim(Cells(5, "S") & " " & Cells(rw, 2))
            'Thursday
            If Cells(rw, "L") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "T") = Application.Trim(Cells(3, "T") & " " & Cells(rw, 2))
            If Cells(rw, "L") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "T") = Application.Trim(Cells(4, "T") & " " & Cells(rw, 2))
            If Cells(rw, "L") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "T") = Application.Trim(Cells(5, "T") & " " & Cells(rw, 2))
            'Friday
            If Cells(rw, "M") = "Y" And Cells(rw, "C") = "X" Then Cells(3, "U") = Application.Trim(Cells(3, "U") & " " & Cells(rw, 2))
            If Cells(rw, "M") = "Y" And Cells(rw, "D") = "X" Then Cells(4, "U") = Application.Trim(Cells(4, "U") & " " & Cells(rw, 2))
            If Cells(rw, "M") = "Y" And Cells(rw, "E") = "X" Then Cells(5, "U") = Application.Trim(Cells(5, "U") & " " & Cells(rw, 2))
        Next
End Sub
 

aestable

New Member
Joined
Apr 19, 2016
Messages
4
It's a thought, thanks Perpa... I could conceivably even populate lists with nested loops to go through columns and lines, and something along the lines of

Code:
Range(col & row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="'string of names here"
BUT... If I make a change to the availability, the lists wouldn't update dynamically, is that correct? I would have to run the macro every time I make a change to repopulate the lists with the updated content?

It's certainly something to explore, thanks for the suggestion!

Regards
AE
 

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
634
It's a thought, thanks Perpa... I could conceivably even populate lists with nested loops to go through columns and lines, and something along the lines of

Code:
Range(col & row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="'string of names here"
BUT... If I make a change to the availability, the lists wouldn't update dynamically, is that correct? I would have to run the macro every time I make a change to repopulate the lists with the updated content?

It's certainly something to explore, thanks for the suggestion!

Regards
AE

Axle,
I did a bit more work on the Data Validation portion of your project. I needed to create 'Lists' for the Data validation to draw from. So I removed the 'Error Message' option from each cell in the Range P3 to U5. Then, using the macro recorder, I copied each of those cells and PasteSpecial>Values to the cells in Range P10 to AG10. I stopped the macro recorder and cleaned up/simplified that code to the following:
Code:
Sub DV_ListsCreate()
    Application.CutCopyMode = False
    Range("P3").Copy
    Range("P10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("P4").Copy
    Range("Q10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("P5").Copy
    Range("R10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("Q3").Copy
    Range("S10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("Q4").Copy
    Range("T10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("Q5").Copy
    Range("U10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("R3").Copy
    Range("V10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("R4").Copy
    Range("W10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("R5").Copy
    Range("X10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("S3").Copy
    Range("Y10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("S4").Copy
    Range("Z10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("S5").Copy
    Range("AA10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("T3").Copy
    Range("AB10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("T4").Copy
    Range("AC10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("T5").Copy
    Range("AD10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("U3").Copy
    Range("AE10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("U4").Copy
    Range("AF10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("U5").Copy
    Range("AG10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = True
    Range("P3:U5").ClearContents     'Clears this range after copied to the 'List' range
End Sub
Then I created 4 formulae to list each person separately that was shown in the combined names which have been moved to cells P10 to AG10. Those formulae are:

P11 =IF(ISERROR(LEFT(P10,FIND(" ",P10)-1)),P10,LEFT(P10,FIND(" ",P10)-1))
P12=IF(ISERROR(RIGHT(P10,LEN(P10)-FIND(" ",P10))),"",RIGHT(P10,LEN(P10)-FIND(" ",P10)))
P13=IF(ISERROR(LEFT(P12,FIND(" ",P12)-1)),"",LEFT(P12,FIND(" ",P12)-1))
P14=IF(ISERROR(RIGHT(P12,LEN(P12)-FIND(" ",P12))),"",RIGHT(P12,LEN(P12)-FIND(" ",P12)))

I copied and pasted each formula across to column AG. Here is what it looked like. I color coordinated it to help me keep cells in sink:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;;">Sheet3</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Sun 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Mon 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Tue 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Wed 4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Thu 5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Fri 6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFF00;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFC000;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #E6B9B8;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #B2A1C7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #66F69D;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #00FF00;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #92D050;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #E46D0A;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #75923C;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #7F7F7F;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #31849B;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FF3399;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Area 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #B8CCE4;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #00B0F0;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FF0000;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #CC00FF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #D8D8D8;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFFFFF;;">P3</td><td style="color: #574123;background-color: #FFFFFF;;">P4</td><td style="color: #574123;background-color: #FFFFFF;;">P5</td><td style="color: #574123;background-color: #FFFFFF;;">Q3</td><td style="color: #574123;background-color: #FFFFFF;;">Q4</td><td style="color: #574123;background-color: #FFFFFF;;">Q5</td><td style="color: #574123;background-color: #FFFFFF;;">R3</td><td style="color: #574123;background-color: #FFFFFF;;">R4</td><td style="color: #574123;background-color: #FFFFFF;;">R5</td><td style="color: #574123;background-color: #FFFFFF;;">S3</td><td style="color: #574123;background-color: #FFFFFF;;">S4</td><td style="border-bottom: 1px solid black;color: #574123;background-color: #FFFFFF;;">S5</td><td style="color: #574123;background-color: #FFFFFF;;">T3</td><td style="color: #574123;background-color: #FFFFFF;;">T4</td><td style="color: #574123;background-color: #FFFFFF;;">T5</td><td style="color: #574123;background-color: #FFFFFF;;">U3</td><td style="color: #574123;background-color: #FFFFFF;;">U4</td><td style="color: #574123;background-color: #FFFFFF;;">U5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #92D050;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #B8CCE4;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #FFC000;;"></td><td style="text-align: right;background-color: #E46D0A;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #00B0F0;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #E6B9B8;;"></td><td style="text-align: right;background-color: #75923C;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #FF0000;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #B2A1C7;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #7F7F7F;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #66F69D;;"></td><td style="text-align: right;background-color: #31849B;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #CC00FF;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #FF3399;;"></td><td style="text-align: right;border-right: 1px solid black;background-color: #D8D8D8;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">Archie Charlie</td><td style=";">Archie Charlie Eric</td><td style=";">Charlie Eric</td><td style=";">Archie Dave</td><td style=";">Archie</td><td style=";">Dave</td><td style=";">Archie Charlie</td><td style=";">Archie Charlie Eric</td><td style=";">Charlie Eric</td><td style=";">Archie Dave</td><td style=";">Archie</td><td style=";">Dave</td><td style=";">Archie Charlie</td><td style=";">Archie Charlie</td><td style=";">Barb Charlie</td><td style=";">Dave</td><td style=";">Eric</td><td style=";">Barb Dave Eric</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">Archie</td><td style=";">Archie</td><td style=";">Charlie</td><td style=";">Archie</td><td style=";">Archie</td><td style=";">Dave</td><td style=";">Archie</td><td style=";">Archie</td><td style=";">Charlie</td><td style=";">Archie</td><td style=";">Archie</td><td style=";">Dave</td><td style=";">Archie</td><td style=";">Archie</td><td style=";">Barb</td><td style=";">Dave</td><td style=";">Eric</td><td style=";">Barb</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">Charlie</td><td style=";">Charlie Eric</td><td style=";">Eric</td><td style=";">Dave</td><td style=";"></td><td style=";"></td><td style=";">Charlie</td><td style=";">Charlie Eric</td><td style=";">Eric</td><td style=";">Dave</td><td style=";"></td><td style=";"></td><td style=";">Charlie</td><td style=";">Charlie</td><td style=";">Charlie</td><td style=";"></td><td style=";"></td><td style=";">Dave Eric</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Charlie</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">Charlie</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">Dave</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";"></td><td style=";">Eric</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">Eric</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";">Eric</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

I thought the previous code I posted was a little volumous, so I tweaked it to the following:
Code:
Sub WhoCanWork2()
Dim rw, n As Integer
Range("P3:U5").clearcontents     'Gets rid of any previous information
    For rw = 3 To 7
        For n = 3 To 5
            'Sunday
            If Cells(rw, "H") = "Y" And Cells(rw, n) = "X" Then Cells(n, "P") = Application.Trim(Cells(n, "P") & " " & Cells(rw, 2))

            'Monday
            If Cells(rw, "I") = "Y" And Cells(rw, n) = "X" Then Cells(n, "Q") = Application.Trim(Cells(n, "Q") & " " & Cells(rw, 2))

            'Tuesday
            If Cells(rw, "J") = "Y" And Cells(rw, n) = "X" Then Cells(n, "R") = Application.Trim(Cells(n, "R") & " " & Cells(rw, 2))

            'Wednesday
            If Cells(rw, "K") = "Y" And Cells(rw, n) = "X" Then Cells(n, "S") = Application.Trim(Cells(n, "S") & " " & Cells(rw, 2))

            'Thursday
            If Cells(rw, "L") = "Y" And Cells(rw, n) = "X" Then Cells(n, "T") = Application.Trim(Cells(n, "T") & " " & Cells(rw, 2))

            'Friday
            If Cells(rw, "M") = "Y" And Cells(rw, n) = "X" Then Cells(n, "U") = Application.Trim(Cells(n, "U") & " " & Cells(rw, 2))
        Next
    Next
Call DV_ListsCreate
End Sub

Notice that the last line before 'End Sub' calls the code to create the DV Lists...so you only have to run one macro to create the DV you wanted. Yes, you will have to run it when you change availability and or personnel names. The formulae in the cells below row 10 in columns P to AG are set up for a maximum of 3 people per day. Also, if you have three people possible, the formulae in row 12 puts the 2nd and 3rd names together in that cell. Then the formulae in rows 13 and 14 work to separate the names in row 12.
When you select a DV cell where 3 names are possible, one of the selections will be those 2nd and 3rd names combined. If you want you can work on removing that, but you don't 'have to' select the combined names.

Not as simple as I first thought...but this is one way to get Data Validation. I would assign the 'WhoCanWork2' code to a button to make it easier to update when you change availability or personnel.
The ' DV_ListsCreate' code should just go in a standard module.
Perpa
 
Last edited:

navic

Active Member
Joined
Jun 14, 2015
Messages
289
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

But I would like to have in each cell a drop-down list with the names of the staff who are qualified to work in that area AND who are available that day.
See if you can help, this example with the helper sheet, without VBA.
Unfortunately, I had to share a file on the download server because I can not explain how I want.
 

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
634
See if you can help, this example with the helper sheet, without VBA.
Unfortunately, I had to share a file on the download server because I can not explain how I want.

navic,
I was unable to download your file...also, I got a 'malicious software' warning, so I am not anxious to try again.
Perpa


Axel,
I found this code (thank your hiker95) while trying to simplify the DV list process. You can use this code in place of the formulae I provided earlier for the Range(P11:AG14).
Perpa

Code:
Sub ReorgData()
' hiker95, 10/04/2013
' http://www.mrexcel.com/forum/excel-questions/731036-how-separate-delimited-lists-into-seperate-columns-using-vbulletin.html
' Perpa modified this code to place delimited lists to same column one beneath the other
Dim c As Range, s
For Each c In Range("P10:AG10")
  s = Split(Trim(c), " ")
  c.Offset(1, 0).Resize(, UBound(s) + 1) = s    'This line puts each name in a separate column
  c.Offset(2, 0) = c.Offset(1, 1)     'This line copies the second name below the first name
  c.Offset(3, 0) = c.Offset(1, 2)     'This line copies the 3rd name below the 2nd name
  Range(c.Offset(1, 1), c.Offset(1, 2)).ClearContents     'This line clears the columns where the 2nd and 3rd names were copied from
Next c
End Sub
 

navic

Active Member
Joined
Jun 14, 2015
Messages
289
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

navic,
I was unable to download your file...also, I got a 'malicious software' warning, so I am not anxious to try again.
Sorry, I have AVG and MBAM programs and I do not have these problems.
Maybe this download server is not problematic (but disable 'check-out' download manager)
 
Last edited:

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
634
Sorry, I have AVG and MBAM programs and I do not have these problems.
Maybe this download server is not problematic (but disable 'check-out' download manager)

navic,
I also have Malware Bytes Anti Malware (MBAM) which is what alerted me to the problem, again. Perhaps you should post your question directly as a new thread and someone else may be able to help you. Good luck.
Perpa
 

navic

Active Member
Joined
Jun 14, 2015
Messages
289
Office Version
  1. 2013
Platform
  1. Windows
Perhaps you should post your question directly as a new thread and someone else may be able to help you.
I do not need help, I answered @aestable.
Maybe my answer in English is not well written?

OK, please all visitors to ignore my posts above and download links if there is a security problem
 
Last edited:

aestable

New Member
Joined
Apr 19, 2016
Messages
4
Thank you Perpa - I will try to tweak it but it seems like it is headed in the right direction... My concern though is that I am doing that for the entire month (31 days) over 20 location assignments, so I would have to loop through 620 columns to fetch all the lists! Not impossible, but tedious. That's why I was initially hoping to populate the lists directly, without first creating lists in cells.

As a side note, to avoid having to run the macro every time a change is made, would it be possible to trigger it with an event, e.g. when the worksheet with the availabilities loses focus? (I forgot to mention that the three tables in my initial post are in three different worksheets)

Thanks again for your suggestions!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top