Data Validation - Two Entries in One Cell

[SimonHughes]

Hello, I have a worksheet to schedule engineers visits to customer premises. Engineers names in the Row 1, dates in Column A. Sometimes an engineer will go to two or three customers on the same day and I need to show that in the single cell.
I am using data validation to pick from a customer list and by using the ALT+Enter function I can add a second customer in the cell with free text (which defeats the object of uniformity). I have changed the "Stop" to "Information" so it does work OK but I would like to know if I can have more than a single entry via data validation in one cell? Example of what I want is below.

TIA>

Date	Engineer 1
Sun 01 Sep 2024
Mon 02 Sep 2024
Tue 03 Sep 2024	Lorenzo Street
Wed 04 Sep 2024
Thu 05 Sep 2024
Fri 06 Sep 2024	BB Belfast (Cathedral Quarter) Westin Another
Sat 07 Sep 2024
Sun 08 Sep 2024

 

[AlphaFrog]

Here is a link to a step-by-step guide to create a multi-entry Data Validation dropdown.
Excel Data Validation - Select Multiple Items

Alternatively, consider putting the additional locations in adjacent columns or have multiple rows with the same date for each additional location. This would give you more flexibility if you wanted to summarize the data in the future e.g. count how many times you visited a location within a given date range..

 

[SimonHughes]

Here is a link to a step-by-step guide to create a multi-entry Data Validation dropdown.
Excel Data Validation - Select Multiple Items

Alternatively, consider putting the additional locations in adjacent columns or have multiple rows with the same date for each additional location. This would give you more flexibility if you wanted to summarize the data in the future e.g. count how many times you visited a location within a given date range..

That was a consideration but with 14 engineers who could all visit multiple sites on the same day we could have 10, 20 or 30 rows for a single date which would be a bit too unwieldy.

 

[kvsrinivasamurthy]

Worksheet event is used.
Validation Range H2:H10. Validation list range K2:K8. Do the validation. Keep the 'Information' as it is.
Then use worksheet event.
Code

Dim S As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If S <> "" Then Target = S & Chr(10) & Target
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H2:H10")) Is Nothing Then
S = Target
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[SimonHughes]

Worksheet event is used.
Validation Range H2:H10. Validation list range K2:K8. Do the validation. Keep the 'Information' as it is.
Then use worksheet event.
Code

Dim S As String

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If S <> "" Then Target = S & Chr(10) & Target
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H2:H10")) Is Nothing Then
S = Target
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

Hi, I have followed these steps, and thanks you, but there is no effect at all? The full range of the sheet is B2:R500, I assume I need to change the VBA to reflect that?

 

[kvsrinivasamurthy]

What's your validation list range. K2:K8 should be replaced by your validation list range. H2:H10 should be replaced by your validation range.