Solved

Creating dynamic range with dependent dropdowns

Posted on 2012-04-02
5
294 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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