Data validation and auto populate for multiple items

[barlevi]

Hi,


I would like to have a data validation cell that allows multiple selections from a pricelist located in another sheet,on another cell I would like to have the price of that items chosen from the pricelist in the data validation cell.


I know how to do this for one item, but for multiple items I need help.


Thanks

 

[konew1]

I would use vlookup to get the price.
Assuming the products are on sheet!2 column A and the prices in column B, the formula, and the product you are interested in is in A1
=VLOOKUP(A1,Sheet2!A$1:B$11,2,FALSE)

Have you done the data validation cell? you said you have done it for 1 item - you just need add more items to the list

 

[barlevi]

I would use vlookup to get the price.
Assuming the products are on sheet!2 column A and the prices in column B, the formula, and the product you are interested in is in A1
=VLOOKUP(A1,Sheet2!A$1:B$11,2,FALSE)

Have you done the data validation cell? you said you have done it for 1 item - you just need add more items to the list

Hi,

Thanks for this reply.
English is not my native language, so maybe I was misunderstood:
Lets say I sell fruits, I have a price list of 15 items, they are all located in one sheet including their prices.
straight forward data validation allows me to choose only one fruit, and by using the VLOOKUP I can get the price populated automatically - which I know how to do.
the problem I have, is that I would like to have more than one item chosen in the data validation, so if someone by apple and orange, I would like both to show in the cell, and the sum should be calculated automatically for both.

The part of setting the data validation to have multiple choices is already setup using VBA code (I have no knowledge with VBA programming, so I copied the code from another forum).
When choosing more than one fruit, the next item is being displayed after a comma is placed automatically (ie. "Apple, Orange" - without the quotes)

I hope this explain it better.

Thanks

 

[konew1]

Ok. I understand what you are asking for, but can't suggest a formula solution. I think you will need vba code and I don't write code.
Cheers

 

[Marcelo Branco]

Could you show us the code and the price list?

M.

 

[barlevi]

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 3 '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


Example of price list:
A B
1 Apple 20
2 Orange 25
3 Melon 35

Thanks in advance

 

[barlevi]

Anyone?