Data Validation with 2 criteria

[Dana M]

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.

 

[muddan madhu]

Validation for Cell D2

Go to data | validation | allow: custom | formula : =AND
(B2<>"",C2<>"",D2<=-1) | uncheck ignore blank | ok

 

[Dana M]

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]

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.

 

[Shane Devenshire]

Hi,

You can use the data validation formula

=(B2:C2<>"")*D2<=-1

However, regardless of what approach you take you will not be able to show
two different messages based on the two warnings you want. Also, this
condition is either Stop or Warn, not both. If you really need that kind of
flexibility you will need to consider VBA.

You already have a response about Absolute, Mixed and Relative references:
B2 or B$2 or $B2 or $B$2.

 

[Dana M]

Thanks for clarifying!

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.

 

[Dana M]

Thanks for the additional help!

Hi,

You can use the data validation formula

=(B2:C2<>"")*D2<=-1

However, regardless of what approach you take you will not be able to show
two different messages based on the two warnings you want. Also, this
condition is either Stop or Warn, not both. If you really need that kind of
flexibility you will need to consider VBA.

You already have a response about Absolute, Mixed and Relative references:
B2 or B$2 or $B2 or $B$2.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire