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?

Try this:

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

From the Excel Main Menu:
<data<validation
....Allow: Custom
....Formula: =AND(G220,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(G220,G2<60)
G3 will use: =AND(G320,G3<60)
etc
G10 will use: =AND(G1020,G10<60)

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

Regards,

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

"Peter" wrote in message
...
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?