Dynamic dropdown?

[Guest]

Hi--

I'd like to create a dynamic dropdown in which the contents of the dropdown
(source) is all the values (unique) in the column where the dropdown
operates. If the user enters a new value, one that isn't yet in the column,
I'd like that value to be added to the dropdown next time. So, let's say that
all the cells from B1 to B50 would have a dropdown. Initially, before any
values are entered into that range, the dropdown would be empty. Then, let's
say the user enters "Hello" into B1. When they go to B2, the dropdown would
contain one option: Hello. Now they can either select Hello from the dropdown
in B2 or they can type in another value, let's say, "World". Let's say they
enter "World". Now when they go to B3, the dropdown will have Hello and World
listed. And so on...

Also, let's say the user enters "World" from the dropdown into cell B3. Now
B1 is Hello, and B2 and B3 are World. The dropdowns in the range would not
list World twice!

Is there a way to do this directly in the spreadsheet without using forms?

Thanks!

 

[Ron Coderre]

If a NON-VBA solution is acceptable, see if something like this helps.
In this example, the input list begins in B2. There is a formula driven
list in Col_D that will build the list.

First...some Range Names

Names_in_Workbook: LU_FlexibleList
Refers to:
=InputSheet!$D$2:INDEX(InputSheet!$D:$D,COUNTIF(InputSheet!$D:$D,"?*")+COUNT(InputSheet!$D:$D),1)

Names_in_Workbook: rngUserInput
Refers to: =InputSheet!$B$2:$B$50

Next, prep the list areas
D1: (column heading, eg List Items)
D2: (manually enter the first item to appear in the dropdown list, eg Hello)

Enter this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just
Enter) in cell
D3:=INDEX(rngUserInput,MATCH(TRUE,ISERROR(IF(ISBLANK(rngUserInput),FALSE,MATCH(rngUserInput,$B$2:$B2,0))),0),1)
(Note: D3 will return #N/A...that is expected)

Copy D3 and paste into D4 and down as far as you think you'll need

B1: Inputs
Select from B2:B50
<data><validation>
Settings_Tab:
Allow: List
Source:
=INDEX(LU_FlexibleList,1):INDEX(LU_FlexibleList,COUNTIF(LU_FlexibleList,"<>#N/A"))
Error_Alert_Tab:
UNcheck: "Show error alert after invalid data is entered."
Click [OK]

To use the list....either click the dropdown and select an item...OR...just
type a new item. It will appear only once in the DV list.

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)