Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm trying to understand data validation using custom formulas. What I don't
get is how to apply validation rules to a range using a custom formula that validates a cell based on the value in that cell. The specific problem is how to reference the individual cell. Suppose validation is being applied to G2:G10. I notice that if I use a formula like "=G2:G1020", that works: cells in that range with values less than 20 are invalid, while cells with values greater than 20 are valid. (I realize there are much easier ways to handle a simple comparison like that; I'm just using an easy comparison to try to understand the general principle. I'd like to create validation functions using complex spreadsheet or custom VBA functions.) OK, so I can just reference the entire range, and Excel knows to evaluate a cell based on the value in just that cell. So, I expect the following should also work: "=AND(G2:G1020,G2:G10<60)". But it doesn't: every cell is considered invalid. I figured out that I can get a cell self-reference using this awkward idiom: "INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)" But it seems there must be some easier way. What am I missing? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
formulas/data validation | Excel Worksheet Functions | |||
Can Data Validation work on cells with formulas? | Excel Discussion (Misc queries) | |||
Data Validation formulas | Excel Discussion (Misc queries) | |||
formulas for data validation | Excel Discussion (Misc queries) | |||
Data Validation using whole numbers from formulas | Excel Discussion (Misc queries) |