Solved

Choices For DropDown

Posted on 2013-01-20
341 Views
Hello All,

Please check the attached file for sample
I have a complex issue regarding dropdowns. I need three dropdowns (dependent). Can you suggest the best vba way to do this?
Quarter dropdown
Month dropdown
Week dropdown
For example:
1.      if user selects Q1 for quarter dropdown, then he ONLY sees M1,M2,M3,M4 as choices for the month dropdown
2.      Then, if user selects M1 for month dropdown, then he ONLY sees W1,W2,W3,W4 as choices for the week dropdown
What I am envisioning – is this possible within Excel’s architecture? Or I am overthinking :(

thank you
dropDownbreakDown.xlsm
0
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 2

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 38799094
See if this method works for you using data validation formulas from ranges.
Copy-of-dropDownbreakDown.xlsm
0

Author Comment

ID: 38799118
Perfect Ssaqibh,

You have proved from time to time that:
The best solution is always the most simplest one

(and I was thinking 100 lines of VB code :(

Can you exaplin whats happening here for the two drop-downs
month dropdown =OFFSET(\$L\$7:\$O\$10,MATCH(\$C\$2,\$L\$7:\$L\$10,0)-1,1,1,3)

week dropdown = =OFFSET(\$L\$10,MATCH(\$C\$3,\$L\$11:\$L\$22,0),1,1,5)

thank you :)
0

LVL 43

Expert Comment

ID: 38799125
Paste the formula in any blank cell.
Select the cell
press F2...this will come to edit mode and will help you see the referred cells
with the edit mode active press F9 and you will see the result of the formula.

If you still have problems understanding then write back.
0

Author Comment

ID: 38799229
Hello Ssaqibh,

Every-time you help me in EE, I always learn something NEW that I was not aware of :)
I never used those shortcut keys before. but now I will. Like here
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27915756.html
I learnt 'STOP' and I have been using that extensively in coding since then
All thanks to you :)

I understood how you set up the logic. Took me a while to figure that as I didn't used offset formula for DV before.

Thank you
0

Author Closing Comment

ID: 38799232
Unmatched BEST Expert Help as always.
0

Author Comment

ID: 38799265
0

Author Comment

ID: 39926043
Hello Syed,

I remembered that you helped me with this complex one last time :)

I have another new question -
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28387216.html#a39925376

Rayne
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month3 days, 3 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.