Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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

On Sat, 8 Mar 2008 12:28:00 -0800, Jeff H.
wrote:

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

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?

Edit Data Validation 2003
 

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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?

Edit Data Validation 2003
 

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

On Sat, 8 Mar 2008 17:32:02 -0500, "T. Valko" wrote:

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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

Edit Data Validation 2003
 

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

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Edit Data Validation 2003
 

Interesting. I've never experienced that.


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file he

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Edit Data Validation 2003
 

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" wrote:

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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?

Edit Data Validation 2003
 

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....


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Interesting. I've never experienced that.


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file he

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Edit Data Validation 2003
 

See Debra's reply.

On her site she notes:

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.



--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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" wrote:

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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?

Edit Data Validation 2003
 

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" wrote:

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....


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Interesting. I've never experienced that.


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file he

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Edit Data Validation 2003
 

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" wrote:

See Debra's reply.

On her site she notes:

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.



--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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" wrote:

Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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?

Edit Data Validation 2003
 

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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" wrote:

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....


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Interesting. I've never experienced that.


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file he

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Edit Data Validation 2003
 

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Jeff H." wrote in message
...
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" wrote:

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....


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Interesting. I've never experienced that.


--
Biff
Microsoft Excel MVP


"Debra Dalgleish" wrote in message
...
And occasionally you'll get that warning message even if the data
validation is all the same. There's a sample file he

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


T. Valko wrote:
Is this the message that you're seeing:

http://img503.imageshack.us/img503/3...messagerx3.jpg

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html