Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
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 "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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation with 2 criteria
Thanks for the additional help!
"Shane Devenshire" wrote: 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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple data validation criteria | Excel Discussion (Misc queries) | |||
Can you create a LIST and DATA VALIDATION CRITERIA in same cell? | Excel Discussion (Misc queries) | |||
Data validation two criteria | Excel Worksheet Functions | |||
data validation list should have opt. to select based on criteria | Excel Worksheet Functions | |||
Is the data validation criteria limited to a few number of items . | Excel Worksheet Functions |