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

Topic

New Reply

How can I short cut a repetitive task in my spreadsheet.

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

36789-1

Kerry

Reply | Quote

Replies

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
        Case “Darwin”, “Nhulunbuy”, “Katherine”
            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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

You are both welcome.

Reply | Quote

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

Reply | Quote