Solved

Creating dynamic range with dependent dropdowns

Posted on 2012-04-02
5
299 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
[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
  • 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 42

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook Free & Paid Tools
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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