Need Help With Drop Down List

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
Hi, so I need help with some data that I currently work with. The data goes like this, for example

[TABLE="width: 465"]
<colgroup><col span="2"><col><col span="4"></colgroup><tbody>[TR]
[TD]VisitDay[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MON-1[/TD]
[TD]Apple[/TD]
[TD]Orange[/TD]
[TD]Pear[/TD]
[TD]Grape[/TD]
[TD]Melon[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]MON-2[/TD]
[TD]Grape[/TD]
[TD]Watermelon[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MON-3[/TD]
[TD]Apple[/TD]
[TD]Durian[/TD]
[TD]etc [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Days[/TD]
[TD="width: 64, bgcolor: transparent"]MON-1[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Fruits[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Orange[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Pear[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Grape[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Melon[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]etc[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
Code:
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
 
Upvote 0
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.
Code:
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!
 
Upvote 0
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
 
Last edited:
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,223,887
Messages
6,175,199
Members
452,617
Latest member
Narendra Babu D

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top