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.

 

[mumps]

Please be more detailed in your explanation by referring to specific cells, rows, columns and sheets. In which sheet (sheet name) and cell is the drop down list? What are the 2 values in the list that you want to use? Which cells and on which sheet do you want to populate?

 

[t1o9n9y1]

Apologies. The sheet name with the drop down list is called Rework Tracker. The drop down list is G5:L40. There are five text entries in the list... ECOS failure - code: 2064 and ECOS failure - code: 2257.

In the fourth sheet, titled ECOS Statistics I have a table A5:C40. Whenever the two entries above are selected from the drop down list, I want these cells to auto populate with said entries.

 

[t1o9n9y1]

ECOS failure - code: 2064 and ECOS failure - code: 2257 are the two entries that when selected I want to populate the table in the fourth sheet.

 

[mumps]

I think that a simple macro would do what you want. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Rework Tracker" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. When you select either of the 2 values from the drop down list, A5:C40 in the "ECOS Statistics" sheet will populate automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G5:L40")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "ECOS failure - code: 2064", "ECOS failure - code: 2257"
            Sheets("ECOS Statistics").Range("A5:C40").Value = Target.Value
    End Select
End Sub

 

[t1o9n9y1]

That has worked, however, when I make a single selection from the drop down menu in Rework Tracker, it populates the entire table in ECOS statistics, cells A5:C40 with the one entry. I needed the table to populate with single entries when they are made. Is this possible?
Thanks so much for your help so far

 

[mumps]

In what order do you want the cells populated, across the rows or down the columns?

 

[t1o9n9y1]

Down the columns. It has done that, it's just filled every cell down the column with the same single entry.

 

[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 = 7 To 12
                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

 

[t1o9n9y1]

So that has worked, there's now only a single entry. However, the entry appears on the fourth sheet in cell G5, not A5.