Add to validation list
Posted by David w on February 22, 2001 9:52 AM
Is it possible to have a drop down validation
list that if the item is not in your list
By entering the item into the cell it will add
it to the list range for future entries?
And if that's possible can you keep the list in
an external workbook that will update when you add
to the drop down list?
Posted by Aladin Akyurek on February 22, 2001 10:47 AM
Enter some items in A from A1 on. Select these cells, activate the option Insert,Name,Define. Type DDList for Names in Workbook and the follwing formula for Refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
This machinery creates a dynamic range to which you might any number of items (although I personally object to such a list when it contains more than say 10 values).
To use it as a dropdown list, select a cell and select the option Data,Data Validation. On Settings tab, select List for Allow and type =DDList as Source. Done.
Aladin
Posted by David w on February 22, 2001 1:48 PM
Aladin,
that's not quite what I had in mind. I need to be in the drop down list
and if I don't see the item in the list, I would like to type it in to
the drop down list and by doing so have it add it to the list.
can this be done?
Posted by Aladin Akyurek on February 22, 2001 2:20 PM
: Is it possible to have a drop down validation : list that if the item is not in your list : By entering the item into the cell it will add : it to the list range for future entries? : And if that's possible can you keep the list in : an external workbook that will update when you add : to the drop down list?
I seem to have misread you. I think what you're asking is a reasonable concept. But it conflicts, I believe, with Excel's idea of input control thru dropdown lists. You can follow a different route that might suit your purpose.
If you designate a cell as the one where to input an item, you can check whether the item entered exists with a formula and if it exists you do whatever needed with that item. If it the doesn't exist, you can first add it to your data and continue processing. If this sounds reasonable to you, let me know. Otherwise, you'll probably need WBA-code that intrusively carries out your original concept. In that case, you need a VBA-expert.
Aladin
Posted by Mary Beth on February 22, 2001 4:48 PM
VBA is totally foreign to me and I am relatively new to Excel. I did manage to create a drop down list of my customers but it will not work it my customer list workbook is closed. Is there a way to do this list without having to having the other workbook open?
