Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative cell reference in custom validation
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative cell reference in custom validation
You could achieve the same effect with a macro linked to the worksheet_change event which deletes the new value if the value to the 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 Sub -- mrice Reserach Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=535686 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
relative cell reference in custom validation
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="") GoBobbyGo wrote: 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? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Copying a work sheet cell reference as relative not absolute? | Excel Discussion (Misc queries) | |||
Cell Reference locking | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |