Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default Dynamic dropdown?

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!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,118
Default Dynamic dropdown?

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(In putSheet!$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(ISBLA NK(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,CO UNTIF(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)



"ppsa" wrote in message
...
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!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Dropdown difficulties Brad Excel Discussion (Misc queries) 0 January 31st 07 04:40 PM
Dynamic Dropdown Part II Brad Excel Discussion (Misc queries) 2 January 30th 07 10:46 PM
Dynamic Dropdown - Part II Brad Excel Discussion (Misc queries) 0 January 30th 07 08:41 PM
Dynamic Dependent Dropdown - one question for Mr. Phillips Brad Excel Discussion (Misc queries) 1 January 3rd 07 11:06 PM
Dynamic Forumlas based on Dropdown Bridget from ATL Excel Discussion (Misc queries) 0 October 31st 06 10:10 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"