Combining formula/functions

Martin Hall Kenny

New Member
Joined
Sep 21, 2014
Messages
10
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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>
 
Upvote 0
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
 
Upvote 0
' 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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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