Drop down list that eliminates two options

momo6

New Member
Joined
May 24, 2020
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi dear all, I have a situation with my table where for each row from 5 to 8 I want to have a dropdown list based on values in column P.
However I want the options "Manager" and "Lead analyst" to be picked only once for each poject, meaning I want to eliminate these two options whenever it is picked for a specific employee and a specific project.
Is there a way I can do this with keeping the table in the same format?
Thank you so much.
1624956208250.png
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You may have to change the range (in red) depending on the number of names. Close the code window to return to your sheet. This approach will warn you if you pick "Manager" or "Lead analyst" more than once for each project.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("F5:K8")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Manager", "Lead analyst"
            If WorksheetFunction.CountIf(Range("F" & Target.Row & ":K" & Target.Row), Target) > 1 Then
                MsgBox ("You can select only one " & Target & " for " & Range("E" & Target.Row) & "." & Chr(10) & "Please make another selection.")
                Target.ClearContents
                Target.Select
            End If
    End Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top