So here is what my challenge is...
I have a list of equipment I use. I have the list created in a table from which i can select multiple values and it will place them into the same cell and separate them by commas (like seen below in column A). To do this I am using the following code:
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
'run code if only one cell was changed
If Target.Count > 1 Then GoTo exitHandler
Select Case Target.Column
Case 2 'this Case line works for column B only
'Case 2, 5, 6 'this Case line works for multiple columns
On Error Resume Next
'check the cell for data validation
Set rngDV = Target.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 oldVal <> "" Then
If newVal <> "" Then
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End Select
exitHandler:
Application.EnableEvents = True
End Sub"
Now what I want to happen when I select the items in Column A is for Column B (Cost) to sum the associated values from the adjacent table (Equipment/Price). Essentially what I want to get out of it is =sum(5.09+50.45+20.00+20.00) but without having to manually input as my actual list has ~100+ Items. Is this possible?
<tbody>
</tbody>
I have a list of equipment I use. I have the list created in a table from which i can select multiple values and it will place them into the same cell and separate them by commas (like seen below in column A). To do this I am using the following code:
"Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
'run code if only one cell was changed
If Target.Count > 1 Then GoTo exitHandler
Select Case Target.Column
Case 2 'this Case line works for column B only
'Case 2, 5, 6 'this Case line works for multiple columns
On Error Resume Next
'check the cell for data validation
Set rngDV = Target.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 oldVal <> "" Then
If newVal <> "" Then
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End Select
exitHandler:
Application.EnableEvents = True
End Sub"
Now what I want to happen when I select the items in Column A is for Column B (Cost) to sum the associated values from the adjacent table (Equipment/Price). Essentially what I want to get out of it is =sum(5.09+50.45+20.00+20.00) but without having to manually input as my actual list has ~100+ Items. Is this possible?
Equipment | Cost | Equipment | Price | ||
Item 1, Item 3, Item 5, Item 5 | Example | Item 1 | 5.09 | ||
Item 2, Item 3 | Item 2 | 4.99 | |||
Item 2, Item 3, Item 4, Item 5 | Item 3 | 50.45 | |||
Item 1, Item 4, Item 5 | Item 4 | 16.95 | |||
Item 5 | 20.00 |
<tbody>
</tbody>