Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

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

    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


    1.jpg

    Kerry

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thankyou Maud this is exactly what I was trying to do. I have expanded it to do a few more things as well.

  4. #4
    New Lounger
    Join Date
    Apr 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •