Tried dynamic named range, works great !!

Still not sure about erase message, but this way I only have to retype
(=Mylist) in the source box.

Thanks for you help.

"T. Valko" wrote:

Try this experiment:

We'll use column H on Sheet1 as the source for a drop down list.

Enter this data starting in H1 to H3: Joe, Sue, Lisa

Create a dynamic named range:
Goto InsertNameDefine
Name: MyList
Refers to:

=Sheet1!$H$1:INDEX(Sheet1!$H:$H,COUNTA(Sheet1!$H:$ H))

OK

Create a data validation drop down list in cell A1.
As the source enter =MyList

Try the drop down out. You'll see it contains Joe, Sue, and Lisa.

Now, add a couple of names to the source range: H4 = Biff, H5 = Rick

Now, try the drop down list in cell A1 again. You'll see that it has updated
to include Biff and Rick.

Ok, so far so good.

Now, edit the source range again, this time delete cells H3, H4 and H5.

Check the drop down list and you'll see that it has updated.

If you edit the source and remove something from the middle of the range
move everything up so that there are no empty cells within the range. It has
to be a contiguous range.


--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
i understand how to use a range of cells to create the data validation, but
not sure about dynamic named range that auto updates.

"T. Valko" wrote:

I don't get that message in Excel 2002. Something you could do :

Select *one* cell that contains the validation.
Goto DataValidation
Edit the source as needed.
OK out

With that cell still selected
Goto EditCopy
Select the other cells that use the same validation
Then, goto EditPaste SpecialValidationOK

If you used a range of cells as the source and used a dynamic named range
then all you would have to do is edit the range and the source would
update
automatically.

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
When I created the list in Data Validation, I just typed the names in
the
source box with a comma between eash name. Did not created a range.

"Gord Dibben" wrote:

Where is the source for the DV list?

From a defined named range like =MyRange?

From a list on a sheet like =A1:A10?

A comma delimited list in the source dialog like joe,mary,pete,agnes?

Change or add to the contents of any of those to get a new list.


Gord Dibben MS Excel MVP

On Sat, 8 Mar 2008 10:54:01 -0800, Jeff H. <Jeff

wrote:

Hi,
I am using Excel 2003 at work and I have a spreadsheet that contains
data
validation dropdown list. I need to edit my list to add co-workers.
When
I
goto Data Validation, I receive a dialog box message saying
(Selected
cells
already contain data validation, do you want to erase data and
continue?). I
do not want to erase the current data, I just want to add to it. When
I
am
using Excel 2000 at home I can edit the data without receiving this
message.
Is there a step with Excel 2003 that is different or a setting I need
to
change to edit my data?