Combining formula/functions

[Martin Hall Kenny]

Hi Guys
Forgive me if the answer to this is obvious...
I am creating a spreadsheet designed to be a staff rota. (Staff members are listed on a seperate sheet). Our service users require care and support at intervals throughout the day and, in order to keep it simple I have divided the day into 15 minute slots - from 0700 to midnight. Staff are allocated to a service user on a one to one basis. Using - =COUNTIF($b$3:$B$53,B3)=1, I have been able to prevent the person populating the spreadsheet from entering the same staff member for more than one service user at a time. My problem is this; I want to limit the information being entered into the cells in question to the staff list I mentioned above. If I use; =(Staff), I get a drop down list that prevents names other than those on the staff list being put in. If I use the 'COUNTIF' above I prevent duplication. I can't find of a way of combining the two and the thing is, I want my cake and eat it too! Is there a way of linking these two formula so that I can both prevent duplicates AND limit entries to the staff list I have created?

Martin

 

[knigget40]

I hope this helps. Below are 2 examples. In the first, create the following macro within the Worksheet. The Worksheet_Change event will fire every time a name (in range B3:B53) is entered/changed:</SPAN>

Option Explicit</SPAN>

Const DATA_ENTRY_RANGE As String = "B3:B53"</SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
Dim numTimesEntered As Long</SPAN>

' If a cell value in DATA_ENTRY_RANGE (i.e., "B3:B53") changes, then execute code</SPAN>
If Not Application.Intersect(Target, Range(DATA_ENTRY_RANGE)) Is Nothing Then</SPAN>
numTimesEntered = WorksheetFunction.CountIf(Range(DATA_ENTRY_RANGE), Target.Value)</SPAN>
If numTimesEntered > 1 Then</SPAN>
MsgBox "Employee " & Target.Value & " has been entered " & numTimesEntered _</SPAN>
& " times"</SPAN>
Target.Value = "" ' uncomment out this line if you want to erase the entry they just made</SPAN>
End If</SPAN>
End If</SPAN>

End Sub</SPAN>



In the second example, if you want to keep the formula “</SPAN>=COUNTIF($b$3:$B$53,B3)=1</SPAN>” within the worksheet then you can use the following worksheet macro instead which assumes the formula is located in through C3:C53.</SPAN>
Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
Dim numTimesEntered As Long</SPAN>

' If a cell value in DATA_ENTRY_RANGE (i.e., "B3:B53") changes, then execute code</SPAN>
If Not Application.Intersect(Target, Range(DATA_ENTRY_RANGE)) Is Nothing Then</SPAN>
If Not Target.Offset(0, 1) Then</SPAN>
numTimesEntered = WorksheetFunction.CountIf(Range(DATA_ENTRY_RANGE), Target.Value)</SPAN>
MsgBox "Employee " & Target.Value & " has been entered " & numTimesEntered & " times"</SPAN>
Application.EnableEvents = False</SPAN>
Target.Value = "" ' uncomment out this line if you want to erase the entry they just made</SPAN>
Application.EnableEvents = True</SPAN>
End If</SPAN>
End If</SPAN>

End Sub</SPAN>

 

[knigget40]

Of course, you still need to use your data validation of =(Staff)

 

[Martin Hall Kenny]

Thanks knigget40... I will give that a shot!

 

[Martin Hall Kenny]

Hi knigget40
I am not getting very far I'm afraid so would you mind taking me through it step by step? I copied and pasted into the macro but I'm obviously doing something (maybe lots of somethings) wrong. I do want to erase any entry made when it conflicts with the conditions we've set.
Thanks matey
Martin

 

[knigget40]

' First, within the Developer -> Visual Basic area, under Microsoft Excel Objects, select the worksheet in which the data entry is to be performed in.
' Make sure you put this code in the worksheet, not in a module:

Option Explicit</SPAN>

Const DATA_ENTRY_RANGE As String = "B3:B53" ' this needs to be the range in which you have data validation for user data entry as you described above</SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
Dim numTimesEntered As Long</SPAN>

' If a cell value in DATA_ENTRY_RANGE (i.e., "B3:B53") changes, then execute code</SPAN>
If Not Application.Intersect(Target, Range(DATA_ENTRY_RANGE)) Is Nothing Then</SPAN>
numTimesEntered = WorksheetFunction.CountIf(Range(DATA_ENTRY_RANGE), Target.Value)</SPAN>

If numTimesEntered > 1 Then
</SPAN>
MsgBox "Employee " & Target.Value & " has been entered " & numTimesEntered </SPAN>& " times"
</SPAN>
Target.Value = "" ' uncomment out this line if you want to erase the entry they just made</SPAN>
End If</SPAN>
End If
End Sub



' Now, select the actual worksheet, click in cell B3 and select a name
' Now click in cell B4 and select the same name as in B3. You should get the warning message from the above macro and the cell will be set to blank