[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Excel: Dynamic drop-down


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)?

1 Solution
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.

Rob HensonIT & Database AssistantCommented:
I have seen the use of the SMALL function to create a list of unique values, just trying to find the previous question.
Rob HensonIT & Database AssistantCommented:
See this previous question, expertly answered by Barry Houdini.


Wasn't the one I was looking for but does the same.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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?

Contextures has a method outlined here that should work for you:

Rob HensonIT & Database AssistantCommented:
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.

Rob H
Rob HensonIT & Database AssistantCommented:
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.
ExpExchHelpAuthor Commented:

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:

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

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

Rob HensonIT & Database AssistantCommented:
The IFERROR function was introduced in 2007 and later, this will have to be re-written using the Excel 2003 syntax =IF(ISERROR(...))


Hope that works.


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

ExpExchHelpAuthor Commented:

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,
Rob HensonIT & Database AssistantCommented:
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.

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now