Formula drops in a cell w/ Data Validation

[donyadaniels76]

I am working on a spreadsheet that has several cells that have data validation. Part of the intention of the cells is to allow the users to select multiple items from the drop-down menu. In order for that to work, I've had to add a macro that allows for several items to be selected from the drop-down menu.
Here is the code for the macro:

Private Sub Worksheet_Change(ByVal Target As Range)Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 2 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If

The users of the spreadsheet wanted what they filled into one cell to auto-fill into the other cells in the column. So, at first I created a macro that basically allowed them click a button and it would auto-fill. However, I think there is an issue with this:

• The button macro fills in all the cells in column 2. I couldn't find a way for the macro to perform an if function. So, if they put names in column 1, I had to have it flow to all of the potential selected cells in column 2. I really just wanted to it to flow to the cell in column 2, if there is a name in column 1.

So, I decided to use an IF formula: =IF(ISTEXT(A13),B12,"") But when I put that formula into the cells and hit enter, the formula disappears.

I know it has something to do with the target value in the macro, but I don't know how to fix it. I need the code in the macro, so they can select several options from the drop-down but I also need the formula to remain.

Any thoughts or suggestions?

 

[donyadaniels76]

Any thoughts on this?