Using drop down boxes to pull values in the same worksheet.

[MarcSwift]

Hi all,

I have a query. In A1 i have a drop down box. In this drop down box i have dates of all the sundays in the year. In columns D1 and E1, F1 and so on, i have the dates of these sundays eg. 17/7/11, 24/7/11 31/7/11.
Under each of these dates i have values (names) and when i select a date on the drop down box, i want the values which relate to the date in columns D,E,F etc to appear under the drop down box.

How do i do this?

Thanks a lot guys

 

[Sahak]

Hi MarcSwift,

Couple questions…

1. do you mean Combo Box in A1 ?

In A1 i have a drop down box

2. in A2 ?

under the drop down box

 

[MarcSwift]

Hi MarcSwift,

Couple questions…

1. do you mean Combo Box in A1 ?



2. in A2 ?

Hello!
THanks for the reply!

I have used a list data validation in A1 and yes i want the values to appear in a2,a3,a4 etc

I havent used a combo box so not sure what the best way of achieving the result is.

THanks

 

[Sahak]

I think the best way is Combo Box

Let assume:

1. your data located in D1:W2 (Dates in first row & values in second row)
2. Combo Box in B1
Then this code will do what you want:

Option Explicit
Private Sub ComboBox1_Change()
    Dim Dt As Single
    Dt = ComboBox1.Value
    Range("A65536").End(xlUp).Offset(1, 0).Value = WorksheetFunction.HLookup(Dt, Range("D1:W2"), 2, False)
End Sub