Solved

Excel: Dynamic drop-down

Posted on 2013-12-11
11
395 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 31

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 31

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

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 31

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 31

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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