Complicated!! - Conditional formatting with nested function

[JP knows excel enough to mess it up]

Ok, i have a sheet with a series of columns that take user input. to the
right of the user input (offset by 0 rows & 3 columns) i have predetermined
limitations. I am attempting to set a conditional format that highlights the
user-editable fields if their values fall outside the paramaters of those in
the next few ranges. The referenced data will eventually be hidden from the
user which is why I want excel to alert the users if they are making
mistakes. What I have been attempting to use is something like this:

=if(offset(indirect(address(column(), row())), 0, 3) = <<some value>>
.....using a formula under conditional formatting
.....using excel 2007
..... for testing purposes I have been using sample text, i.e. ="test"

excel accepts the formula but no formatting takes place. Where might my
mistake be?

 

[Pete_UK]

I'm not sure what your "predetermined limitations" might be, but
suppose the user is entering numbers and your conditions relate to a
maximum value allowed. Assume the data input is in columns A to C
starting with row 2, and your max values are in columns D to F.

Highlight the cells in columns A to C that you will use for input -
suppose this is A2:C10, with A2 as the active cell. Bring up the
conditional formatting dialogue box and choose Formula Is rather than
Cell Value Is in the first box, then enter this formula:

=OR(ISTEXT(A2),A2>D2)

Click on the Format button, choose the Patterns tab (for background
colour) and choose a colour, then OK your way out. Excel will
automatically adjust the cell references for the other cells in the
selected range. Now if you enter a number in A2 which is larger than
that in D2, or if you enter text into A2, the cell should change
colour. Similarly for the other cells, although those in column B will
be compared with column E and those in column C will be compared with
column F.

Hope this helps.

Pete

 

[Max]

.. excel accepts the formula but no formatting takes place.

.. Where might my mistake be?

Perhaps you mis-understood where the actual target cell is referenced in
your OFFSET expression, since it is row/col sensitive depending on which cell
it is placed (this can get confusing)?

I could get something like this working in CF for cell C1:
=OFFSET(INDIRECT(ADDRESS(COLUMN(),ROW())),0,3)="text"
where the target cell was D3, which contained: text
It was D3 because the indirect bit resolved to "A3", the offset's anchor
cell, and the col param was 3

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik