Auto populating empty cells with specific text selection from drop down list

[t1o9n9y1]

I am trying to auto populate a group of empty cells with specific selections from a drop down list. The total list contains five entries, two of which I want to populate the empty cells when they are selected. I have tried the =IF formula, but it only seems to work within the same sheet. Whenever I try to do it from another sheet I get the #VALUE ! error. Any suggestions would be much appreciated.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...into-table-on-separate-sheet.html#post5024091

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[t1o9n9y1]

Apologies, also posted on Excelforums.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G5:L40")) Is Nothing Then Exit Sub
    Dim rng As Range, x As Long, y As Long
    Select Case Target.Value
        Case "ECOS failure - code: 2064", "ECOS failure - code: 2257"
            For x = 1 To 3
                For Each rng In Sheets("ECOS Statistics").Range(Sheets("ECOS Statistics").Cells(5, x), Sheets("ECOS Statistics").Cells(40, x))
                    If rng = "" Then
                        rng = Target
                        y = y + 1
                        Exit For
                    End If
                Next rng
                If y = 1 Then Exit For
            Next x
    End Select
End Sub