multiple drop down menu with auto population

Z

zeysing

New Member
Joined
Apr 9, 2013
Messages
2
Hi,
First time poster, hope I don't irritate someone if my question is redundant.
I have used a spreadsheet that has done this so I know if is possible, I just don't have access to that spreadsheet anymore.

example
I have 3 properties, prop 1, prop 2, prop 3

I want a spreadsheet I can select which property from a drop down menu
after selecting which property I want to choose which month
after selecting which month I want my stats to auto populate for the selected month into the appropriate cells.

I've tried using vlookup and had a little success with a single drop down menu and having that auto populate, still struggling with the 2. I'm not sure if I've given enough detail, help please.
Thanks,
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Thank you for your post. But before I answer that, I have a question:
Are there selected Months for each of the properties? Or All months are listed for each of the property?

In that case, we can use named ranges for this. We have to create a list of Months for each of the properties and named this as different named ranges.

Thanks!
 
Upvote 0
Yes each property will have different results depending on the month. Okay so creating a name range I think I can figure out. Once I've named the range what would be the next step to tie everything to the drop down menu, vlookup??
Thanks!
 
Upvote 0
I would prefer using OFFSET function instead of the Vlookup. If I understood this correctly, the lookup range would consist of multiple criteria and months. Then, selecting a property and month on the dropdown will list all information if the lookup range will have the selected property and selected month in it.

In case that is true, since we are dealing with two categories now (Property and month), you can concatenate this two first. You need to add a column on the data source as the lookup range where property and month will be concatenated from each row. E.g., Refer to the table below. Say data source table is our lookup value and Output table is where I wanted to populate information. I have two solutions for this:

Solution1:

PropertyProp1--> Dropdown1
MonthApril--> Dropdown2
OUTPUTDATA SOURCE
NoPropertyMonthNameIncomeNo if it is under the selected criteriasCONCATENATEPropertyMonthNameIncome
1Prop1AprilA551Prop1AprilProp1AprilA55
2Prop1AprilG92 Prop2AprilProp2AprilB85
3 Prop3AprilProp3AprilC69
4 Prop1MayProp1MayD58
Prop2MayProp2MayE63
Prop3MayProp3MayF94
2Prop1AprilProp1AprilG92
Prop2AprilProp2AprilH60
Prop3AprilProp3AprilI88
Prop1MayProp1MayJ73
Prop2MayProp2MayK64
Prop3MayProp3MayL56

<colgroup><col><col><col span="5"><col><col><col span="5"></colgroup><tbody>
</tbody>

Formulas for Solution1:
PropertyProp1--> Dropdown1
MonthApril--> Dropdown2
OUTPUTDATA SOURCE
NoPropertyMonthNameIncomeNo if it is under the selected criteriasCONCATENATEPropertyMonthNameIncome
1=IFERROR(OFFSET($G$6,MATCH($A7,$H$7:$H$18,0),MATCH(B$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A7,$H$7:$H$18,0),MATCH(C$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A7,$H$7:$H$18,0),MATCH(D$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A7,$H$7:$H$18,0),MATCH(E$6,$H$6:$M$6,0)),"")=IF($C$2&$C$3=$I7,MAX($H$6:H6)+1,"")=J7&K7Prop1AprilA55
2=IFERROR(OFFSET($G$6,MATCH($A8,$H$7:$H$18,0),MATCH(B$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A8,$H$7:$H$18,0),MATCH(C$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A8,$H$7:$H$18,0),MATCH(D$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A8,$H$7:$H$18,0),MATCH(E$6,$H$6:$M$6,0)),"")=IF($C$2&$C$3=$I8,MAX($H$6:H7)+1,"")=J8&K8Prop2AprilB85
3=IFERROR(OFFSET($G$6,MATCH($A9,$H$7:$H$18,0),MATCH(B$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A9,$H$7:$H$18,0),MATCH(C$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A9,$H$7:$H$18,0),MATCH(D$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A9,$H$7:$H$18,0),MATCH(E$6,$H$6:$M$6,0)),"")=IF($C$2&$C$3=$I9,MAX($H$6:H8)+1,"")=J9&K9Prop3AprilC69
4=IFERROR(OFFSET($G$6,MATCH($A10,$H$7:$H$18,0),MATCH(B$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A10,$H$7:$H$18,0),MATCH(C$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A10,$H$7:$H$18,0),MATCH(D$6,$H$6:$M$6,0)),"")=IFERROR(OFFSET($G$6,MATCH($A10,$H$7:$H$18,0),MATCH(E$6,$H$6:$M$6,0)),"")=IF($C$2&$C$3=$I10,MAX($H$6:H9)+1,"")=J10&K10Prop1MayD58
=IF($C$2&$C$3=$I11,MAX($H$6:H10)+1,"")=J11&K11Prop2MayE63
=IF($C$2&$C$3=$I12,MAX($H$6:H11)+1,"")=J12&K12Prop3MayF94
=IF($C$2&$C$3=$I13,MAX($H$6:H12)+1,"")=J13&K13Prop1AprilG92
=IF($C$2&$C$3=$I14,MAX($H$6:H13)+1,"")=J14&K14Prop2AprilH60
=IF($C$2&$C$3=$I15,MAX($H$6:H14)+1,"")=J15&K15Prop3AprilI88
=J16&K16Prop1MayJ73
=J17&K17Prop2MayK64
=J18&K18Prop3MayL56

<colgroup><col><col><col span="3"><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Solution2:
PropertyProp1--> Dropdown1
MonthApril--> Dropdown2
OUTPUTDATA SOURCE
NoPropertyMonthNameIncomePropertyMonthNameIncome
1Prop1AprilA55Prop1AprilA55
2Prop1AprilG92Prop2AprilB85
3 Prop3AprilC69
4 Prop1MayD58
Prop2MayE63
Prop3MayF94
Prop1AprilG92
Prop2AprilH60
Prop3AprilI88
Prop1MayJ73
Prop2MayK64
Prop3MayL56

<colgroup><col><col span="4"><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
Formulas for Solution2:

PropertyProp1--> Dropdown1
MonthApril--> Dropdown2
OUTPUTDATA SOURCE
NoPropertyMonthNameIncomePropertyMonthNameIncome
1=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A7),MATCH(B$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A7),MATCH(C$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A7),MATCH(D$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A7),MATCH(E$6,$H$6:$K$6,0)),"")Prop1AprilA55
2=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A8),MATCH(B$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A8),MATCH(C$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A8),MATCH(D$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A8),MATCH(E$6,$H$6:$K$6,0)),"")Prop2AprilB85
3=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A9),MATCH(B$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A9),MATCH(C$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A9),MATCH(D$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A9),MATCH(E$6,$H$6:$K$6,0)),"")Prop3AprilC69
4=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A10),MATCH(B$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A10),MATCH(C$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A10),MATCH(D$6,$H$6:$K$6,0)),"")=IFERROR(OFFSET($G$6,SMALL(IF(($H$7:$H$18=$C$2)*($I$7:$I$18=$C$3),ROW($H$7:$H$18)-ROW($H$6),""),$A10),MATCH(E$6,$H$6:$K$6,0)),"")Prop1MayD58
Prop2MayE63
Prop3MayF94
Prop1AprilG92
Prop2AprilH60
Prop3AprilI88
Prop1MayJ73
Prop2MayK64
Prop3MayL56
Hope this helps!
Cheers

<colgroup><col><col span="4"><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Column data selection drop-down
Replies
5
Views
308
SanjayGMusafir
SanjayGMusafir
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
236
Jeffrey Mahoney
Jeffrey Mahoney
X
  • Question
Populate info from one tab using cell as a reference
Replies
1
Views
210
etaf
etaf
R
  • Solved
adapt drop down list after selection is made
Replies
7
Views
200
Peter_SSs
Peter_SSs
S
  • Question
Drop Down Selection "Yes" to subtract from cell
Replies
3
Views
234
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,214,981
Messages
6,122,565
Members
449,089
Latest member
Motoracer88

Share this page

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