LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 349
Default Data validation formulas

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formulas/data validation CHRIS...in trouble Excel Worksheet Functions 1 December 30th 07 01:33 PM
Can Data Validation work on cells with formulas? Ruben Excel Discussion (Misc queries) 1 July 26th 07 06:34 PM
Data Validation formulas Lele Excel Discussion (Misc queries) 4 March 4th 07 02:10 PM
formulas for data validation Luke Excel Discussion (Misc queries) 3 July 25th 06 03:01 PM
Data Validation using whole numbers from formulas brodiemac Excel Discussion (Misc queries) 4 April 20th 06 02:02 PM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"