adding elements to an existing list

[aj]

Hello. I have a multi-sheet spreadsheet. One sheet contains pre-
defined "lists", which other sheets in the spreadsheet use to
populate
drop-down boxes.

The problem is, I am trying to extend one of these lists to include 2
new values.


I have tried every conceivable way (select the list, and drag the
right-bottom corner to cover the 2 new entries, etc).


Even the Data --> List --> "Resize List" option is greyed out.


I suspect I have a problem with someone having previously "protected"
certain aspects of this spreadsheet.


Can someone offer advice?


Thanks,
-AJ

 

[Jim Rech]

You have to go into the properties of each drop down and change its
associated list range. Right click on the drop down and pick Properties.

--
Jim
| Hello. I have a multi-sheet spreadsheet. One sheet contains pre-
| defined "lists", which other sheets in the spreadsheet use to
| populate
| drop-down boxes.
|
| The problem is, I am trying to extend one of these lists to include 2
| new values.
|
|
| I have tried every conceivable way (select the list, and drag the
| right-bottom corner to cover the 2 new entries, etc).
|
|
| Even the Data --> List --> "Resize List" option is greyed out.
|
|
| I suspect I have a problem with someone having previously "protected"
| certain aspects of this spreadsheet.
|
|
| Can someone offer advice?
|
|
| Thanks,
| -AJ
|

 

[Don Guillett]

IF?? the list is a defined name such as
=mylist
then it should have been designed to allow for additions/deletions to the
list. Goto insert>name>define to see if the list name is there. In the
refers to box> touch the f2 key and modify to suit or rewrite with a self
adjusting defined name
If this is not the case, I misunderstood.

 

[Gord Dibben]

See Debra Dalgleish's site for a couple of sample workbooks on methods of adding
to existing lists.

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0021 - Update Multiple Validation Lists -- In a workbook with multiple data
validation lists, type a new value in a cell that contains data validation, and
it's automatically added to the appropriate source list, and the list is sorted;
a macro automates the list updates. DataValListAddSortMulti.zip 12 kb

DV0038 - Flexible Item List -- Use formulas to automatically add new unique
items to a data validation dropdown list. Sample file from Ron Coderre.
DataValFlexList.zip 7kb 18-Feb-06


Gord Dibben MS Excel MVP

 

[aj]

Don,

This fixed my problem, thanks for the wonderful answer! I would have
never found the answer otherwise!

-AJ