Need Help With Drop Down List

[andersen_yunan]

Hi, so I need help with some data that I currently work with. The data goes like this, for example

VisitDay	Fruits
MON-1	Apple	Orange	Pear	Grape	Melon	etc
MON-2	Grape	Watermelon	etc
MON-3	Apple	Durian	etc

<colgroup><col span="2"><col><col span="4"></colgroup><tbody>
</tbody>

and I want to make drop down list for every time I change the Visit day to desired day, the fruits list would show up like this vertically

Days	MON-1
Fruits
Apple
Orange
Pear
Grape
Melon
etc

<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>
</tbody>

I tried to use Vlookup but it's not effective and cost me time. Is there any solution to this example? any help would be greatly appreciated!
Thanks

 

[mumps]

Since I don't know how your data is set up, the macro assumes the following: the drop down list is in cell A1 of Sheet1, the data is in Sheet2. The sheet names and the cell containing the drop down list may have to be changed to suit your needs. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down list in A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim foundVal As Range
    Dim lColumn As Long
    Set foundVal = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).ClearContents
    If Not foundVal Is Nothing Then
        lColumn = Sheets("Sheet2").Cells(foundVal.Row, Columns.Count).End(xlToLeft).Column
        Sheets("Sheet2").Range(Sheets("Sheet2").Cells(foundVal.Row, 2), Sheets("Sheet2").Cells(foundVal.Row, lColumn)).Copy
        Sheets("Sheet1").Cells(2, 1).PasteSpecial Transpose:=True
    End If
    Application.CutCopyMode = False
End Sub

 

[andersen_yunan]

Since I don't know how your data is set up, the macro assumes the following: the drop down list is in cell A1 of Sheet1, the data is in Sheet2. The sheet names and the cell containing the drop down list may have to be changed to suit your needs. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down list in A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim foundVal As Range
    Dim lColumn As Long
    Set foundVal = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).ClearContents
    If Not foundVal Is Nothing Then
        lColumn = Sheets("Sheet2").Cells(foundVal.Row, Columns.Count).End(xlToLeft).Column
        Sheets("Sheet2").Range(Sheets("Sheet2").Cells(foundVal.Row, 2), Sheets("Sheet2").Cells(foundVal.Row, lColumn)).Copy
        Sheets("Sheet1").Cells(2, 1).PasteSpecial Transpose:=True
    End If
    Application.CutCopyMode = False
End Sub

Hi, here is the sample data that I need to work with. I tried copy your code but it does not work

http://www.mediafire.com/file/ptzsy53465235bv/Sample.xlsx

Thanks!

 

[Andrew Fergus]

Hi

You can do this with an INDEX/MATCH formula. This is like a combination of VLOOKUP and HLOOKUP.

Assuming your data is in the range A2:J4 (3 days high with 9 columns for fruit, amend to suit)

then below your data (or on another sheet), select the cell where you want the day to be (I used B9 on the same sheet), select menu option Data > Data Validation > Data Validation > in the pop up box select "List" under "Allow" and in the box "Source:" select the range A2:A4 > Ok. This forces you to select one of the days from column A.

Then in cell B10 enter the following formula:
=INDEX($B$2:$J$4,MATCH($B$9,$A$2:$A$4,0),ROW(A1))
and copy this down (say) 9 rows.

Any time you change the selection in B9 the list will auto update with the list of fruit. Change the range and references to suit.

I trust this helps.

Andrew

 

[andersen_yunan]

Hi

You can do this with an INDEX/MATCH formula. This is like a combination of VLOOKUP and HLOOKUP.

Assuming your data is in the range A2:J4 (3 days high with 9 columns for fruit, amend to suit)

then below your data (or on another sheet), select the cell where you want the day to be (I used B9 on the same sheet), select menu option Data > Data Validation > Data Validation > in the pop up box select "List" under "Allow" and in the box "Source:" select the range A2:A4 > Ok. This forces you to select one of the days from column A.

Then in cell B10 enter the following formula:
=INDEX($B$2:$J$4,MATCH($B$9,$A$2:$A$4,0),ROW(A1))
and copy this down (say) 9 rows.

Any time you change the selection in B9 the list will auto update with the list of fruit. Change the range and references to suit.

I trust this helps.

Andrew

Hi, Andrew

Thank you so much for your help. It works perfectly!