relative cell reference in custom validation

[Guest]

I want to set up a custom validation rule for a group of cells so that if the
value of the cell immediately to the left is "Y", nothing can be entered.

Let's say I want this rule in cells J10:J100.

So I highlight that range, go to data-->validation, set it to custom and put
the formula =or(i10<>"Y",j10="") in.

I hit ok, and check the validation in cell J10 and now it says
=OR(S65425<>"Y",T65425="") (!)

The obvious workaround is to now go and highlight JUST J10, go to
data-->validation, set it to custom, check the little box that says "apply
these changes to all other cells with the same settings", and fix the
validation formula.

But is there an easier way to make sure the relative references come out ok?

 

[mrice]

You could achieve the same effect with a macro linked to th
worksheet_change event which deletes the new value if the value to th
left is Y.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 2 Then
If Target.Offset(0, -1) = "Y" Then
Target.Value = ""
End If
End If
End Su

 

[Debra Dalgleish]

When you create the data validation formula, refer to the active cell --
you can see its address in the name box, to the left of the formula bar.

Maybe you selected from cell B100, up to cell J10, and cell B100 was the
active cell. In that case, the formula would be:

=OR(A100<>"Y",B100="")