# Thread: If cell 1 contains a value make Cell 2 = a value

1. ## If cell 1 contains a value make Cell 2 = a value

When I enter Darwin, Nhulunbuy, or Katherine into Cell C Cell D should = Top End
When I enter Alice Springs or Tennant Creek into Cell C - Cell D Should = Central Australia

1.jpg

Kerry

2. Kerryg,

This can be done with some code placed in the Worksheet_Change event subroutine of the sheet's module. If you place any value in column C (C1 to C5000), the code will evaluate the input and place the correct value in the adjacent cell in column D.

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C1:C5000")) Is Nothing Then
Select Case Target
Target.Offset(0, 1) = "Top End"
Case "Alice Springs", "Tennant Creek"
Target.Offset(0, 1) = "Central Australia"
End Select
End If
End Sub```
Or you can enter the following formula in D1 and copy down
=IF(OR(C1="Darwin",C1="Nhulunbuy",C1="Katherine"), "Top End",IF(OR(C1="Alice Springs",C1="Tennant Creek"),"Central Australia",""))

HTH,
Maud

3. Thankyou Maud this is exactly what I was trying to do. I have expanded it to do a few more things as well.

4. ## thnks so much

Hi i got thank u too cause i was exacly looking for a formula like this but i just couldnt get it right

5. If your list is long or would be expanded in the future, I think creating a table of the location and region and then use a Vlookup formula to get the results from the table. I think it makes expanding and upkeep easier. Editing the table would not require changing any of the VLookup formulas.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•