Solved

Choices For DropDown

Posted on 2013-01-20
7
334 Views
Last Modified: 2014-03-13
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
Comment
Question by:Rayne
  • 5
  • 2
7 Comments
 
LVL 43

Accepted Solution

by:
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

by:Rayne
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

by:Saqib Husain, Syed
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Rayne
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

by:Rayne
ID: 38799232
Unmatched BEST Expert Help as always.
0
 

Author Comment

by:Rayne
ID: 38799265
0
 

Author Comment

by:Rayne
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now