Validating a Not Equal To Condition
Given a column that is named "Type" (G1:G50), I tried to enter a validation
rule that does not allow "ULT" to be entered, I tried <"ULT" but that doesn't work also tried =if(g1<"ULT","") and that doesn't work. I would ultimately like to have 5 phrases that would not be allowed. Am I restricted to using VBA? I'm just trying to find the easiest way without alot of code. Any help would be appreciated.... Thanks Ambassador |
Validating a Not Equal To Condition
Select G1:G50 (G1 active, then apply DV,
using Allow: Custom, Formula: =--EXACT(G1,"ULT")<0 Should work fine -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Mike Josephson" wrote in message ... Given a column that is named "Type" (G1:G50), I tried to enter a validation rule that does not allow "ULT" to be entered, I tried <"ULT" but that doesn't work also tried =if(g1<"ULT","") and that doesn't work. I would ultimately like to have 5 phrases that would not be allowed. Am I restricted to using VBA? I'm just trying to find the easiest way without alot of code. Any help would be appreciated.... Thanks Ambassador |
Validating a Not Equal To Condition
Hi Mike,
When applying Data Validation, select then entire range to have the validation and then apply the validation as if only for the first cell of the selection. Excel looks after applying correct formula to remaining cells in the selection. Select the range G1:G50 and then Data Validation - Custom. Use the following formula for multiple strings to be excluded. (I have used 3 strings to exclude. Add as many as you like with comma between them although there is an upper limit but not sure what that is.) =AND(G1<"ULT",G1<"ABC",G1<"DEF") -- Regards, OssieMac |
Validating a Not Equal To Condition
=AND(G1<"ULT",G1<"ABC",G1<"DEF")
Add as many as you like with comma between them although there is an upper limit but not sure what that is. The limit would be the number of arguments the AND function takes which is version dependent* or a total formula length of 255 characters. Whichever occurs first. Excel 2007 = 255 arguments All other versions = 30 arguments -- Biff Microsoft Excel MVP "OssieMac" wrote in message ... Hi Mike, When applying Data Validation, select then entire range to have the validation and then apply the validation as if only for the first cell of the selection. Excel looks after applying correct formula to remaining cells in the selection. Select the range G1:G50 and then Data Validation - Custom. Use the following formula for multiple strings to be excluded. (I have used 3 strings to exclude. Add as many as you like with comma between them although there is an upper limit but not sure what that is.) =AND(G1<"ULT",G1<"ABC",G1<"DEF") -- Regards, OssieMac |
All times are GMT +1. The time now is 08:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com