Having cells change their contents according to what the contents of other cells are

I think you need to look at one of these Functions - VLOOKUP or CHOOSE.

See

http://www.excel-it.com/excel_functions.htm

I hope you are familiar with VBA. Copy and paste the following code in the code module for the sheet you are working with (right click the sheet tab and select 'View Code'). Change the X_ and Y_ Values correspondingly as desired. You may also change the column index for the X_ and Y_Values by changing the const declaration values of lX_Col and lY_Col

Option Explicit

Private Const lX_Col As Long = 1, lY_Col As Long = 2

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Column
    Case lX_Col
        Dim lThisIndex As Long, varThisY As Variant
        If Exists(Target, X_Values, lThisIndex) And Not IsEmpty(Target) Then
            varThisY = Y_Values(lThisIndex)
            Me.Cells(Target.Row, lY_Col) = varThisY
        Else
            Me.Cells(Target.Row, lY_Col) = Empty
        End If
    End Select
End Sub

Private Function Exists _
    ( _
        ByVal element As Variant, _
        SingleRankArray As Variant, _
        Optional ByRef ZeroBasedIndex As Variant = Empty _
    ) _
As Boolean
    Exists = False
    If IsArray(SingleRankArray) Then
        Dim lUbound As Long, lLBound As Long, lCount As Long
        Dim ThisEle As Variant
        lUbound = UBound(SingleRankArray): lLBound = LBound(SingleRankArray)
        For lCount = lLBound To lUbound
            ThisEle = SingleRankArray(lCount)
            If element = ThisEle Then
                ZeroBasedIndex = lCount - lLBound
                Exists = True
                Exit Function
            End If
        Next lCount
    End If
End Function

Private Function X_Values() As Variant
    X_Values = Array(0, 1, 5, 9, 19)
End Function

Private Function Y_Values()
    Y_Values = Array("Zero", "One", "Five", "Nine", "Nineteen")
End Function

Edit: NVM, see this.

This is the code for your worksheet that I think you need.

The validation on activation will help you or your user select the proper text.
In addition to the X_ and Y_ value functions, the Link_Values function is also introduced to hold the hyperlink addresses. The Y_Links are derived from the title of the link web pages.

I hope you understand the logic. ENJOY!

Option Explicit

Private Const lX_Col As Long = 5, lY_Col As Long = 7

Private Sub Worksheet_Activate()
    Dim rngValidate As Excel.Range
    Set rngValidate = Me.Columns(lX_Col)
    With rngValidate.Validation
        .Delete
        .Add xlValidateList, xlValidAlertInformation, , Join(X_Values, ",")
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = False
        .ShowError = False
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngY As Excel.Range
    Select Case Target.Column
    Case lX_Col
        Dim lThisIndex As Long, varThisY As Variant, varThisLink
        If Exists(Target, X_Values, lThisIndex) And Not IsEmpty(Target) Then
            varThisY = Y_Values(lThisIndex)
            varThisLink = Link_Values(lThisIndex)
            Set rngY = Me.Cells(Target.Row, lY_Col)
            rngY = varThisLink
            With rngY.Hyperlinks
                .Delete
                .Add rngY, varThisLink, , _
                    "Click to go to tips on " & CStr(Target), _
                    varThisY
            End With
            Target.EntireColumn.AutoFit
            rngY.EntireColumn.AutoFit
        Else
            Me.Cells(Target.Row, lY_Col) = Empty
        End If
    End Select
End Sub

Private Function Exists _
    ( _
        ByVal element As Variant, _
        SingleRankArray As Variant, _
        Optional ByRef ZeroBasedIndex As Variant = Empty _
    ) _
As Boolean
    Exists = False
    If IsArray(SingleRankArray) Then
        Dim lUbound As Long, lLBound As Long, lCount As Long
        Dim ThisEle As Variant
        lUbound = UBound(SingleRankArray): lLBound = LBound(SingleRankArray)
        For lCount = lLBound To lUbound
            ThisEle = SingleRankArray(lCount)
            If element = ThisEle Then
                ZeroBasedIndex = lCount - lLBound
                Exists = True
                Exit Function
            End If
        Next lCount
    End If
End Function

Private Function X_Values() As Variant
    X_Values = Array _
    ( _
        "Additional Mathematics", _
        "Biology", _
        "Chemistry", _
        "English Language", _
        "English Literature", _
        "French", _
        "History", _
        "Latin", _
        "Mathematics", _
        "Physics", _
        "Religious Studies", _
        "Spanish" _
    )
End Function

Private Function Y_Values()
    Y_Values = Array _
    ( _
        "OCR>Qualifications>Free Standing Maths...", _
        "Edexcel International Qualification: GCSE", _
        "Edexcel International Qualification: GCSE", _
        "AQA GCSE English B Specs...", _
        "AQA GCSE English Lit...", _
        "AQA GCSE French Assesment Materials", _
        "OCR>Qualification>GCSE>History B...", _
        "OCR>Qualification>GCSE>Latin...", _
        "Edexcel International Qualification: GCSE", _
        "Edexcel International Qualification: GCSE", _
        "Edexcel International Qualification: GCSE", _
        "AQA GCSE Spanish Assesment Materials..." _
    )
End Function

Private Function Link_Values()
    Link_Values = Array _
    ( _
        "http://www.ocr.org.uk/qualifications/freestandingmathsquals" & _
              "/additional_mathematics/documents.html#past_papers", _
        "http://www.edexcel-international.org/quals/igcse/4325/", _
        "http://www.edexcel-international.org/quals/igcse/4335/", _
        "http://www.aqa.org.uk/qual/gcse/eng_b_assess.php", _
        "http://www.aqa.org.uk/qual/gcse/eng_lit_b_assess.php", _
        "http://www.aqa.org.uk/qual/gcse/french_a_assess.php", _
        "http://www.ocr.org.uk/qualifications/gcse/history_b_modern_world/", _
        "http://www.ocr.org.uk/qualifications/gcse/latin/documents.html", _
        "http://www.edexcel-international.org/quals/igcse/4400/", _
        "http://www.edexcel-international.org/quals/igcse/4420/", _
        "http://www.edexcel.org.uk/quals/gcse/rs/gcse/1481/", _
        "http://www.aqa.org.uk/qual/gcse/span_a_assess.php" _
    )
End Function

I can't see why you need VBA to do this. A simple VLOOKUP or CHOOSE would work.