I'm having difficulty getting an INDIRECT reference to a cell that contains a dynamic maximum for data validation to work in Excel 2007. Here's what I'm using:
Setting: Custom w/ Allow Blanks Checked
Data: Is not selectable and contains the value "Between"
Formula: <=INDIRECT("R"&CELL("row")&"C"&CELL("col")+3,0)
The value entered must be less than or equal to the value contained 3 columns over.
Any suggestions very much appreciated. Thank you ...... Rick
I'd rather use a conditionnal formatting : INDIRECT is quite weird when going "live" whereas conditionnal formatting are easy to apply.
When setting such a formatting, keep in mind that the formula, just remove the "$".
I.e :
A1 : 3
A2 : 29
A3 : 16
A4 : (to be set up)
Select A4 :
Format -> conditionnal formatting
Value must be less or equal than =A1
be aware that when clicking A1, it will write $A$1, that is not wanted. Then make the format "RED" with a pink background, and no mistake will be made.
