Link to home
Start Free TrialLog in
Avatar of Escanaba
EscanabaFlag for United States of America

asked on

Excel 2007 Formula to Pull Range of Data Based on Drop Down Selection

Please see attached as an example.  I am looking for a formula that looks at what is selected in the drop down cell (B3) and will pull all of the elements of the description on sheet 2.  For example, if Peach is selected on sheet 1 then two cells in column C would show Fuzzy and Pit.  If Apple is selected, then 3 cells to the right of the drop down in column C would show Red, Sweet and Cooking.  If a VB code is required that works too.

Thanks!!!
EE-Example.xlsx
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 3 And Target.Column = 2 Then
        Range("C3:C1000").ClearContents
        lastRow = Sheets("Sheet2").Cells(Rows.Count, "D").End(xlUp).Row
        j = 3
        
        For i = 2 To lastRow
            If Sheets("Sheet2").Cells(i, 3) <> "" Then
                v = Sheets("Sheet2").Cells(i, 3)
            End If
            If v = Target.Value Then
                Cells(j, 3) = Sheets("Sheet2").Cells(i, 4)
                j = j + 1
            End If
        Next
    End If
End Sub

Open in new window

Why would Peach have two cells in Column C, butApple would have details on a Row?
is this what you mean?
EE-Example-b.xlsm
Avatar of Escanaba

ASKER

Ryan - That appears to be spot on.  What would I change in your code to move the description results to a different column/cell?  For example, if I wanted the results to appear starting in cell B6.
Is this what you mean using formulas
EE-Example.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!