Goto Data validation Same ERROR

[Kate]

Hi, I'm puzzled by this process:

I click Edit/GoTO/special/ data validation/ Same and several
cells are found. With these cells still selected, I click
menu item Data/Validation and receive the warning "The
selection contains more than one type of validation. Erase
current settings and continue?"

Why would these cells have been selected if they had
DIFFERENT data validations????

Thanks for any input -Kate

 

[Earl Kiosterud]

Kate,

The Goto - Special - Data Validation identified any cells with any type of
Data Validation settings. They don't have to have the same data validation
settings. But if you have them all selected, and set Data Validation, then
the old settings in the cells will be replaced by your new ones.

 

[Kate]

Earl, thanks for responding, but what I had said was that
when I checked SAME rather than the default ALL under the
goto special data validation, I got this error. If I had
checked ALL I would expect that. Any more ideas?? -Kate

Kate,

The Goto - Special - Data Validation identified any cells with any type of
Data Validation settings. They don't have to have the same data validation
settings. But if you have them all selected, and set Data Validation, then
the old settings in the cells will be replaced by your new ones.

Hi, I'm puzzled by this process:

 

[Earl Kiosterud]

Kate,

You did say Same. I missed that. Ah-HYUK! (archaic for "Doh!")

I just tried a little test situation, but wasn't able to get such a failure
as you describe. GOTO correctly selected only the cells whose Data
Validation settings matched those of the active cell. I included a complete
row, a complete column, and a partial column, overlapping, trying to
confound it. Then with the cells selected from that GOTO, Data - Validation
did not indicate different settings -- it correctly indicated the Data
Validation settings. I'm using XL 2002.

Perhaps if you can describe your settings, the cells layout, we can see if
it will fail on our Excels. Also, indicate the release of Excel you're
using.

You might want to search the knowledge base at www.microsoft.com.

 

[Kate]

Earl,
I don't know what you mean by this statement: "I included
a complete row, a complete column, and a partial column,
overlapping"

What I do is select a single cell, then GoTo/Special/Data
Validation/Same

and three cells get selected. Yet if I then choose
Data/Validation I get the error that the selection contains
more than one type of validation.

This doesn't occur for all the cells in the sheet; for some,
it works correctly. I did an experiment: I copied a block
of four cells containing the three that got selected as
having the same type of validation but which then gave me
the subsequent error, to a new sheet. It worked correctly
on the new sheet; no subsequent error with warning that all
validation would be removed if I proceeded. What could be
going on here??

I'm using Excel 2003 SP1.

Thank you for persisting with this problem,
Kate

 

[Earl Kiosterud]

Kate,

You're welcome, but I don't think I'm going to be much help.

By "complete column," I mean I selected an entire column (clicked in the
column header). I did the same with a row in row 5, which, not
surprisingly, intersected the column. I also included the first 10
cells in another column, so there were two intersections. I don't know if
that mattered. I put the same Data Validation in these cells, then set up
the Validation, then used Edit - Go to, as we did. It didn't mess up as
yours did.

I also tried three cells. It worked OK. But then it sometimes does for you
too.

You might want to put on Office Service Pack 1. You never know. You can
send me the workbook if you want. I'll see if it fails on XL2002.

 

[Debra Dalgleish]

Kate,

I was able to recreate your error, and put my sample workbook here:

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

As you found, I could copy the cells to a different sheet, and the
problem disappeared.

Debra

 

[Earl Kiosterud]

Debra,

I downloaded your sample workbook. It fails on my XL2002. But I don't have
to go through selection via the Edit - Goto path. I need only select the
cells. It seems that it thinks the first cell is different than the rest.

 

[Debra Dalgleish]

Earl,

The Edit>Go To is used to demonstrate that Excel thinks they're all the
same data validation, but then disagrees with itself.

 

[Earl Kiosterud]

Debra,

Yes, but I think just selecting the cells and doing Data - Validation
demonstrates that too. The Validation settings appear to be the same. It
did here. It's strange that copying the cells elswhere makes it stop doing
that -- you get the "more than one type of validation" message."
Interestingly, you have to copy to another sheet -- copying to the same
sheet results in the same error. But if I copy them to the same sheet, then
delete (not clear) the originals, or Cut/Paste the original cells, the
copied cells don't demonstrate the error.

This is more difficult than the airspeed velocity of the swallow.

 

[Earl Kiosterud]

Debra,

Oh, yeah. I see your point. I didn't see the part about Excel disagreeing
with itself. Excel seems to have schizophrenia. There seem to be two bugs
here.

 

[Debra Dalgleish]

Earl,

I'm glad you were able to see my point, after talking among yourselves.
<g>. The Go To Same seems to work correctly, selecting all cells with
the same validation as the active cell. I can't see any pattern to the
incorrect "more than one type of validation" message.

Debra

 

[Earl Kiosterud]

Debra,

While driving, I realized that there aren't two bugs as I'd said. Edit -
GoTo works correctly, as you said -- the validations settings are in fact
the same. It's just Data Validation that's bugged.