Data validation formulas

[Peter]

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:G10>20", 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:G10>20,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?

 

[Ron Coderre]

Try this:

Select G2:G10....with G2 as the Active Cell.

From the Excel Main Menu:
<data><validation>
....Allow: Custom
....Formula: =AND(G2>20,G2<60)
....Set the Input Message (if any) and Error Alert options
....Click [OK]

Each cell in G2:G10 will use its respective version of the DV formula:
G2 will use: =AND(G2>20,G2<60)
G3 will use: =AND(G3>20,G3<60)
etc
G10 will use: =AND(G10>20,G10<60)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)