Solved

Excel: Dynamic drop-down

Posted on 2013-12-11
11
397 Views
Last Modified: 2013-12-19
Experts,

I need some help with a "dynamic" drop-down list in Excel.

Please see attached XLS for details/background.

Does anyone how to address the two questions (again, see XLS)?

Thanks,
EEH
DynamicDropDown.xls
0
Comment
Question by:ExpExchHelp
11 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39711471
You could use code to create a unique list (using advanced filter) in another range and then just sort that and use it as the source for your dropdown.

Regards,
Rory
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711552
I have seen the use of the SMALL function to create a list of unique values, just trying to find the previous question.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711591
See this previous question, expertly answered by Barry Houdini.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28276705.html

Wasn't the one I was looking for but does the same.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:ExpExchHelp
ID: 39711617
Rory -- any suggestions for the required code?

Rob -- I've looked the example from Barry... hmh, that answer doesn't seem to exactly fit what I'm trying to achieve.   Maybe I'm missing something.

Additional thoughts?

EEH
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39711627
Contextures has a method outlined here that should work for you:

http://www.contextures.com/xlDataVal03.html
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711663
Just applying a bit of logic and it would seem that it is currently circular.

Your list in column A is populated by a dropdown based on previous entries in column A; but you don't want duplicates in the dropdown, so can only choose items that have already been listed.

Or do you want to be able to add items and thus have them in the dropdown for future entries but not duplicated.

See attached.

However, this method does throw a slight problem; highlighted by the two entries of New York. I have added them both manually to the list but deliberately mistyped the first entry, omitting the space. This has now appeared in the dopdown list options twice.

Thanks
Rob H
Copy-of-DynamicDropDown.xls
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711672
Should have expanded further on my file.

Two dynamic range names, List and Cities

List - Refers to:
=OFFSET('Dynamic Drop-Down'!$A$1,1,0,COUNTA('Dynamic Drop-Down'!$A:$A)-1,1)

Creates a range as big as the list in column A.

Cities - Refers to:
=OFFSET('Dynamic Drop-Down'!$G$1,0,0,COUNTA('Dynamic Drop-Down'!$G$1:$G$20)-COUNTIF('Dynamic Drop-Down'!$G$1:$G$20,""),1)

Creates a range as big as the list in Column G

The formulae in column G refer to the List range and extract single items, but not in Alphabetical order. If you look at the cells below New York, the formulae go down to row 20, the same as the original list.

The Dropdown in column A then refers to the shortened Cities range.
0
 

Author Comment

by:ExpExchHelp
ID: 39713803
Rob:

Thanks for the additional info... I've checked out the attached XLS but it doesn' t seem to work for me (I use Excel 2003).   Please see attached screenshot w/ error displays.

Also, clicking on the function bar shows the following:
=_xlfn.IFERROR(INDEX(List,MATCH(0,INDEX(COUNTIF(G$1:G1,List),0),0)),"")    

Is the "xlfn" an Excel 2007/2010 element?

If above assumption is correct, is there a way it could be done in Excel 2003?

EEH
Snapshot.jpg
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39713869
The IFERROR function was introduced in 2007 and later, this will have to be re-written using the Excel 2003 syntax =IF(ISERROR(...))

=IF(ISERROR(INDEX(List,MATCH(0,INDEX(COUNTIF(G$1:G1,List),0),0))),"",INDEX(List,MATCH(0,INDEX(COUNTIF(G$1:G1,List),0),0))

Hope that works.

Thanks
Rob

EDIT: Double checked in file for bracket placement and have:

=IF(ISERROR(INDEX(List,MATCH(0,INDEX(COUNTIF(G$1:G1,List),0),0))),"",INDEX(List,MATCH(0,INDEX(COUNTIF(G$1:G1,List),0),0)))
0
 

Author Comment

by:ExpExchHelp
ID: 39714707
Rob:

Fantastic... this works great (i.e., your provided code).

Final question... can the list (column G) automatically be resorted upon entering a new value in column A?   That would ensure the values in the drop-down would be in ASC order.

Thanks in advance,
EEH
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39714771
The sort would have to be on column A as the formula in column G merely pulls the first unique, second unique, third etc..

I assume there is going to be additional data alongside column A which would make it impractical to sort there.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

816 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

13 Experts available now in Live!

Get 1:1 Help Now