Formula in dropdown list

aestable · Apr 20, 2016

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 1	Area 2	Area 3
Archie	X	X
Barb			X
Charlie	X	X	X
Dave	X		X
Eric		X	X

2) The same list of staff members, with the days of the month on which they are available, e.g.:

	Sun 1	Mon 2	Tue 3	Wed 4	Thu 5	Fri 6
Archie	Y	Y	Y	Y	Y	N
Barb	N	N	N	N	Y	Y
Charlie	Y	N	Y	N	Y	N
Dave	N	Y	N	Y	N	Y
Eric	Y	N	Y	N	N	Y

3) A calendar with the different areas, which I have to fill with the names of the staff:

	Sun 1	Mon 2	Tue 3	Wed 4	Thu 5	Fri 6
Area 1	Archie	Archie	Charlie	etc
Area 2	Charlie		Archie
Area 3	Eric	Dave	Eric

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

Perpa · Apr 21, 2016

aestable said:
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 1 Area 2 Area 3
Archie X X
Barb X
Charlie X X X
Dave X X
Eric X X

<tbody>
</tbody>

2) The same list of staff members, with the days of the month on which they are available, e.g.:

Sun 1 Mon 2 Tue 3 Wed 4 Thu 5 Fri 6
Archie Y Y Y Y Y N
Barb N N N N Y Y
Charlie Y N Y N Y N
Dave N Y N Y N Y
Eric Y N Y N N Y

<tbody>
</tbody>

3) A calendar with the different areas, which I have to fill with the names of the staff:

Sun 1 Mon 2 Tue 3 Wed 4 Thu 5 Fri 6
Area 1 Archie Archie Charlie etc
Area 2 Charlie Archie
Area 3 Eric Dave Eric

<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

Excel 2007

B

C

D

E

F

G

H

19

Sun 1

Mon 2

Tue 3

Wed 4

Thu 5

Fri 6

20

Area 1

Archie Charlie

Archie Dave

Archie Charlie

Archie Dave

Archie Charlie

Dave

21

Area 2

Archie Charlie Eric

Archie

Archie Charlie Eric

Archie

Archie Charlie

Eric

22

Area 3

Charlie Eric

Dave

Charlie Eric

Dave

Barb Charlie

Barb Dave Eric

Sheet1

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 · Apr 21, 2016

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 · Apr 22, 2016

aestable said:
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:

Excel 2007

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

1

Sheet3

2

Sun 1

Mon 2

Tue 3

Wed 4

Thu 5

Fri 6

3

Area 1

4

Area 2

5

Area 3

6

7

P3

P4

P5

Q3

Q4

Q5

R3

R4

R5

S3

S4

S5

T3

T4

T5

U3

U4

U5

8

9

10

Archie Charlie

Archie Charlie Eric

Charlie Eric

Archie Dave

Archie

Dave

Archie Charlie

Archie Charlie Eric

Charlie Eric

Archie Dave

Archie

Dave

Archie Charlie

Barb Charlie

Dave

Eric

Barb Dave Eric

11

Archie

Charlie

Archie

Dave

Archie

Charlie

Archie

Dave

Archie

Barb

Dave

Eric

Barb

12

Charlie

Charlie Eric

Eric

Dave

Charlie

Charlie Eric

Eric

Dave

Charlie

Dave Eric

13

Charlie

Dave

14

Eric

Sheet1

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

navic · Apr 23, 2016

aestable said:
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 · Apr 23, 2016

navic said:
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 · Apr 24, 2016

Perpa said:
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)

Perpa · Apr 24, 2016

navic said:
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 · Apr 25, 2016

Perpa said:
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

aestable · Apr 26, 2016

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!

Formula in dropdown list

aestable

New Member

Excel Facts

Perpa

Well-known Member

aestable

New Member

Perpa

Well-known Member

navic

Active Member

Perpa

Well-known Member

navic

Active Member

Perpa

Well-known Member

navic

Active Member

aestable

New Member

Similar threads

Forum statistics

Share this page

Formula in dropdown list

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock