?
Solved

Dynamic Drop-down Lists in Excel

Posted on 2016-10-23
11
Medium Priority
?
105 Views
Last Modified: 2016-10-25
Hi.

I want to workshop, with a group of colleagues, some strategies to meet various objectives.
Some strategies exist and just need to be selected, and some new ones will be identified in the workshop.

What I would like is the flexibility to either select the strategy from the drop-down list, or insert a new one (next to the objective, not directly into the drop-down list) and have the drop-down list update automatically to include the new strategy and enable it to be selected next time.

Can this be done and is so how?

I have attached a example file that hopefully someone can demonstrate how suggested solution works
Dropdown-List.xlsx
0
Comment
Question by:gh_user
[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
  • 6
  • 5
11 Comments
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41856608
You can achieve this with the help of VBA.
In the attached, you will find a new tab called List which will contain the existing list of Strategies and assigned to a dynamic named range called Strategies.
On Sheet1, you will find the drop down list in column B.
So you may select a strategy from the drop down list or you may input a new strategy in the cell instead of selecting any preexisting strategy and the added strategy would be automatically inserted in the List tab and the drop down list would be automatically updated so that next time the newly added strategy would be available for the user to select from the drop down.
Please refer to the attached.
Dropdown-List.xlsm
0
 

Author Comment

by:gh_user
ID: 41856849
Hi Neeraj

A big thanks for the quick correct answer.  You nailed it.
I dont know how to use VBA...obviously its time I learn.
I tried to do a crash course then but it might take me a bit of time to learn.
Would you be able to do a favour for me?
Could you further develop the spreadsheet so it can do the same for 6 columns (ie a separate dynamic drop down list for each column).  I have attached the file to show 6 columns.  I dont know headings yet so have named 'Heading 1 to 6'.  I will work out headings before the workshop.  Then in the workshop will endeavour to complete each cell under each heading.  Can you make it such that I can add extra rows.
Would be grateful for this help as workshop is tomorrow.
If you like, I can create a new question for this.
Dropdown-List_rev-1.xlsm
0
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41856931
Okay. Please find the attached where you will find the drop down lists in all the 6 columns on Sheet1.
Replace the source lists on List tab with your actual list in each column, right now you will find some dummy values over there.
Dropdown-List_rev-1.xlsm
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:gh_user
ID: 41857715
Hi Neeraj

Thanks so much for further developing the spreadsheet, and doing so promptly.  Now getting it ready for use today!
0
 

Author Closing Comment

by:gh_user
ID: 41857717
Very practical and timely help
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41857887
You're welcome. Glad to help.
0
 

Author Comment

by:gh_user
ID: 41858526
Hi Neeraj.  Can I ask one more question.
Are there any suggestions to not break what you have done?
For instance, with Sheet 1, I added many rows within the shaded part. I assume that is OK.  But after adding much content in each cell, not all of it appears in the drop down lists. Dont know why.
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41858536
If you add content in any cell, it will appear on the List sheet and in the drop down only if the cell has a drop down list.
So before adding content down the rows, copy a cell in that column and paste special Validation and once a blank cell contains a drop down list, any content added in that cell would be appear in the drop down next time if it doesn't already exist.
0
 

Author Comment

by:gh_user
ID: 41858553
Thanks
I have a lot of rows to add.  Is there a way to do this quickly rather than one cell at a time.
I tried copying and pasting a whole row but no success
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41858594
To do it fast, select the last row with drop down list in six columns and drag the selection down.
0
 

Author Comment

by:gh_user
ID: 41859463
Thanks Neeraj.  Much appreciated.
0

Featured Post

Technology Partners: 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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.

Join & Ask a Question