Data Validation with 2 criteria
Thanks for clarifying!
"muddan madhu" wrote:
formula should be =AND(B2<"",C2<"",D2<=-1) - in this case if you
copy and use paste speical, validation it will change the cell
reference automatically.
if your formula is =AND(B$2<"",C$2<"",D$2<=-1) - this is Absolute
referencing, so formula will not change copied to different cell.
On Feb 7, 11:17 pm, Dana M wrote:
That worked! Thanks very much. However, now I need to put this validation
in about 150 rows and 20 columns - but not every row and column that
currently has the validation in cell D2. If I click he box "Apply to all
others with the same settings", the validation would be on some total rows.
If I copy, paste special, validation, the formula isn't right - doesn't
change D2 to D3, D4, etc. for subsequent rows, or to E2, F2, etc. for
subsequent columns. Do you know of a way to get the validation in just all
my Data Entry cells without me having to type the formula in each individual
cell?
"muddan madhu" wrote:
Validation for Cell D2
Go to data | validation | allow: custom | formula : =AND
(B2<"",C2<"",D2<=-1) | uncheck ignore blank | ok
Dana M wrote:
Here is an example of my spreadsheet:
A B C D E
Expense Type Owner Qtr1 Qtr2
Paper O WB (15.0) (17.0)
Stone (5.0) 1.0)
Columns B & C, TYPE and OWNER, are drop down lists of valid Expense Types
and Expense Owners. I've used Data Validation to STOP if a selection isn't
made from the list. Columns D and E are data entry columns, with Data
Validation set at WARNING if expense isn't greater than 1. The settings are
Allow Decimal less than 1. However, the user may override the warning and
proceed with entering positive numbers. This is ok. However, I want to add
a restriction - if no selections of Type and Owner are made in columns B and
C, I want to add a STOP with a message - "Please enter Type and Owner before
entering Expense." In the example I've given, the expensex for STONE in Line
3 could not have been entered until Columns B and C had entries from the drop
down boxes. How can this be done? I have intermediate skills and am using
Excel 2003. Thank you in advance for any advice you can give.
|