Adding data to a cell depending on the value of another cell

[liampog]

Hi

In one sheet in the workbook I have a list of customer names in A2:A1001 (Named: customer_names) with their corresponding membership numbers in B2:B1001 (Named: member_numbers).

In another sheet on the workbook I have a Data Validation (LIST) set up to create dropdown menus in the cells (example cell A2) to choose the customer name in the named range or to be able to type any customer name.

Is there any way in the adjacent cell to the Data Validation dropdown cell to be able to automatically fill the member number (example in cell B2) if a known name is chosen from the dropdown?

I think it will be a Worksheet_Change which will do a search when the customer name cell is changed (either by typing a name manually or selecting from the dropdown) and if it finds the name in the Named Range on the other sheet, it automatically changes the value of the cell to equal that of the membership number. If it doesn't find the name of the customer, it does nothing and allows the user to just manually enter a membership number.

I hope this makes sense,
Liam

 

[WhoCanDo]

In B2 enter
=VLOOKUP(A2,customer_group,2,FALSE)

customer_group is the named group of A2:B1001 on workbook 1

 

[liampog]

Hi there

Although this works, because it's a formula it doesn't allow for a manually entered number to be put in by the user.

I think I need a Worksheet_Change event monitoring the A2 (customer name dropdown cell) that will search for the value in A2 in the worksheet containing the list of customer names and numbers.

If it finds it, then it changes the value of B2 to the correct membership number for the customer name.

But it should also allow for a manual entry of membership number should it NOT find the customer name in the list.

Liam

 

[WhoCanDo]

In VBAProject /Microsoft Excel Objects/Sheet2 paste this:

Sub Worksheet_Change(ByVal Target As Range)
'
If (ActiveCell.Address = "$A$2") Then
FoundName = False
i = 0
Do While Not (FoundName) And (i <= 1001)
i = i + 1
If (Sheets("Sheet1").Cells(i, 1).Value = Range("A2").Value) Then
Range("B2").Value = Sheets("Sheet1").Cells(i, 2).Value
FoundName = True
End If
Loop
Else
Range("B2").ClearContents
End If
End Sub

It's the closest I could get for you.