Solved

Creating dynamic range with dependent dropdowns

Posted on 2012-04-02
5
296 Views
Last Modified: 2012-04-02
Hello All
Need help regarding dynamic ranges for drop-downs. There are specific work types. Each work type can have several activities. I created named ranges to help. As user selects a work type, the possible set of options appears for that specific work type ¿ shows up in [Activity] column as a drop down. Now whenever I am creating a dynamic range for the set of activity for [Managerial] work type, the activity drop down is not working.
The activity list for each work type will expand; hence a dynamic range for those activity sets is needed. But when I do it, the drop down doesn’t work.
Thanks in advance
-R
DynamicRange4DropDown.xlsx
0
Comment
Question by:Rayne
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:leptonka
ID: 37798215
Hi,
I created you the attached using tables. You can add new items to any of your lists.

Cheers,
Kris
DynamicRange4DropDown-s.xlsx
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 50 total points
ID: 37798316
As an add-on to the great solution Kris has helped you out with, you might like this article, if you are going to have an extensive list for your drop down validation list, and you'd like to ensure its a unique list that is sorted.  There's an add-in utility at the bottom fro DynamicDV!

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

Please vote YES if you find it helpful.

Cheers,

Dave
0
 

Author Comment

by:Rayne
ID: 37798410
Hello Kris & David,

Thanks you for your replies and David for your enhanced dropdown idea.
Thanks for your help.

I didn’t understand how you made the dependent dropdowns as having dynamic sources in the original attached file [dynamicrange4dropdown]. If you could explain that will be great. Further, I see that I can’t edit the Range for the names ranges. I guess that’s because of something you did to make the dynamic source work.

Thank you
R
0
 
LVL 7

Accepted Solution

by:
leptonka earned 450 total points
ID: 37798537
Hi,

I used Tables. You can see the 3 columns are coloured, has filter and has a small arrow at the bottom-right corner. There are some features of tables.
Table automatically captures new data added to the next empty cell at the bottom - so it is dynamic itself.
When you click on a cell within a table you will see a new menu in the header of your Excel window: Table Tools. If you click on it, you will find a Resize button at the left hand side - you can edit the table range with it.

Hope it helps. (Sorry if my english is not perfect - I am Hungarian :-)
Cheers,
Kris
0
 

Author Comment

by:Rayne
ID: 37798694
Thank you Kris ;) This was very helpful and I Vote Yes for Davids add-in as well
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 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