Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated!! - Conditional formatting with nested function
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated!! - Conditional formatting with nested function
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),A2D2) 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 On Sep 7, 10:01*pm, JP knows excel enough to mess it up t.com wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated!! - Conditional formatting with nested function
.. 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)="te xt" 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 --- "JP knows excel enough to mess it up" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Functions for Conditional Formatting Formula | Excel Worksheet Functions | |||
Conditional Formatting Nested Formulas | New Users to Excel | |||
Complicated conditional formatting if numbers match across a data set..How? | Excel Worksheet Functions | |||
Complicated conditional formatting if numbers match across a data set..How? | Excel Discussion (Misc queries) | |||
Nested functions in conditional formatting formulae | Excel Discussion (Misc queries) |