![]() Named ranges for a cleaner syntaxĪnother way to lock references is is to use named ranges, since named ranges are automatically absolute. Just make sure you use an absolute address to keep the input cell address from changing. You can then change D2 to any priority you like, and the conditional formatting rule will respond instantly. For example, in this case, you could put "Bob" into cell D2 and then rewrite the formula like so: =$D5=$D$2 ![]() Instead you can use another cell as an "input" cell to hold the value so that you can easily change it later. Note that you don't have to hard-code any values that might change into the rule. When the value in column D for in a given row is "Bob", the rule will return TRUE for all cells in that row and formatting will be applied to the entire row. When the rule is evaluated for each of the 40 cells in B5:E12, the row will change, but the column will not.Įffectively, this causes the rule to ignore values in columns B, C, and E and only test values in column D. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address, with column D locked and the row left relative. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |