Solved

Excel: Dynamic drop-down

Posted on 2013-12-11
11
402 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
[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
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 33

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 33

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
Independent Software Vendors: 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!

 

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 33

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 33

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 33

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 33

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

Independent Software Vendors: 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 …
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…
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…

705 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