Solved: 'Custom' data validation

[tpoynton]

Greetings - trying to use a 'custom' data validation with a formula. I am trying to validate that only numbers formatted as numbers are entered into a column (they would likely be pasted), and not numbers formatted as text.

I've never used the custom data validation option...here's what I came up with as the 'allow' value

=IF(ISNUMBER(A2), 1, 0) = 1

it only checks A2 - any way to have the formula work for all cells in the column?

I'd rather not use a worksheet change event, but if that's the best option I can go that route too...


I am doing this because sort on the Mac does not support sorting text as numbers...otherwise I wouldnt care what the format was.

[mdmackillop]

I entered your formula in A2. When I selected A2 to A20 I was prompted to extend the range which put in the relevant cell references.

[tpoynton]

Thanks Malcolm...took me a while, but I was finally able to see what you mean. HOWEVER, I can still cut and paste numbers formatted as text...also tried using istext.

also tried protecting the worksheet and disabling the formatting options, but you can still cut and paste to change the number format.

looks like a worksheet change event is inorder...

[mdmackillop]

The paste will be overwriting the validation I suppose, so I don't see another way.

[Bob Phillips]

Tim,

That is the major flaw with DV, you can paste over the cell and the DV is not triggered.

Also, your formula only needs to be

=ISNUMBER(A2)

[tpoynton]

Thanks Malcolm and Bob...it's 'solved'!

[YellowLabPro]

Tim,
I was following your post to see how it would get resolved.
How did you solve it? I was able to do what I think I understood what you wanted it to do, but intrested to see how you resolved it and if we match.

Doug

[tpoynton]

Hi Doug - I've almost finished a solution, but will likely need some help (never used worksheet change event before); I'll likely post over the weekend. i've got it working for copying and pasting or single-cell entry by themselves, but am having trouble getting them to work in a single sub... I learn much better when I struggle with things for a while before getting help. I'm Mr. Mom today, and naps are limited these days, but I'm counting on getting to it over the weekend. I'll pm the post, or a solution if i figure it out...

[YellowLabPro]

Great.... Look forward to seeing it.
I followed your instructions and copied pasted into the column a text value and it would not allow it w/ the validation setup.

[YellowLabPro]

Tim,
I found this article about helping to deal w/ the validation flaw:
http://www.j-walk.com/ss/excel/tips/tip98.htm

[tpoynton]

Thanks Doug - that's pretty neat! It does prevent DV from being overridden...I can probably expand it to do what I need, since I actually do need to allow people to cut and paste data into there.