Excel: Dynamic drop-down

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
ExpExchHelpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
 
Rory ArchibaldCommented:
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
 
Rob HensonFinance AnalystCommented:
I have seen the use of the SMALL function to create a list of unique values, just trying to find the previous question.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rob HensonFinance AnalystCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
NBVCCommented:
Contextures has a method outlined here that should work for you:

http://www.contextures.com/xlDataVal03.html
0
 
Rob HensonFinance AnalystCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
All Courses

From novice to tech pro — start learning today.