Edit Data Validation 2003

[Jeff H.]

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?

 

[Gord Dibben]

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

 

[Jeff H.]

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.

 

[T. Valko]

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

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

With that cell still selected
Goto Edit>Copy
Select the other cells that use the same validation
Then, goto Edit>Paste Special>Validation>OK

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.

 

[Jeff H.]

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

 

[Gord Dibben]

And you cannot add to those by typing more names?

My Excel 2003 allows that to be done and accepts the new names happily.

I have never seen that particulae message about "erasing".


Gord

 

[T. Valko]

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 Insert>Name>Define
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.

 

[Jeff H.]

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]

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3095/dvmessagerx3.jpg

That happens if you select a range of cells and the data validation is not
the same in all of the selected cells.

 

[Gord Dibben]

Never knew that existed!

Guess I have just not selected a range of cells with different DV.

I should have asked more questions<g>


Gord

 

[Debra Dalgleish]

And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file here:

http://www.contextures.com/xlDataValSame.html

 

[T. Valko]

Interesting. I've never experienced that.

 

[Jeff H.]

Yes, that is the message I receive when trying to edit data validation using
excel 2003. But it's the same data list I've used with excel 2000. With excel
2000 I do not get the message.

 

[T. Valko]

In brand new files with the exact same setup I can't reproduce that behavior
in either Excel 2002 or 2007 (all SPs installed).

Everything works as expected.

Hmmm....

 

[T. Valko]

See Debra's reply.

If you copy the group of cells to a different worksheet,
the error message doesn't appear.

That might not be practical or feasible to do.

At this point I don't have any other suggestions. At least if you use the
dynamic range then all you have to do is type in the range name.

 

[Jeff H.]

As I was looking around my spreadsheet I found my problem. when you mentioned
something about cells not contain the same validation I noticed cells J1 & J2
do not contain data validation. They are used as labels for the column. I
guess that would be the reason for the message. This is the first time I
received it with excel 2000.

Your solution with the dynamic name range solved my problem. I've have
applied it to my workbook and all 12 sheets work correctly. Now I can add or
remove names in one location and it auto updates the list in all 12 sheets.

Thanks again, you have been very helpful

 

[Jeff H.]

As I was looking around my spreadsheet I found my problem. when you mentioned
something about cells not contain the same validation I noticed cells J1 & J2
do not contain data validation. They are used as labels for the column. I
guess that would be the reason for the message. This is the first time I
received it with excel 2000.

Your solution with the dynamic name range solved my problem. I've have
applied it to my workbook and all 12 sheets work correctly. Now I can add or
remove names in one location and it auto updates the list in all 12 sheets.

Thanks again, you have been very helpful

 

[T. Valko]

Glad you got it straightened out. Thanks for letting us know.

 

[Ju6034]

I am not sure if this is the right forum but I have a question about somehow
protecting the data validation (drop down list) in excel. I dont want the
user to delete the cell and the validation. I just want the user to edit the
cell. Is this possible? Thanks