Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Complex Q
I am trying to create a Data Validation on entry which will only allow an entry if certain parameters are met, these parameters are based on values in certain cells. What I am try is for entries in Cell H10, allow an entry IF:-
1) If value in K10 = "C/D", then (2) below does not apply 2) A value is already entered firstly in J10 3) The value being entered in H10 has a max of 2 decimal places, eg user can enter 1000 or 1000.12, but not 1000.123 I have a part of the validation required, as detailed below, but I can figure how where to place the parameter (1) above =AND(J10<"",H10=0.01,H10<=40000,LEN((IF(ISERROR( RIGHT(H10,LEN(H10)-FIND(".",H10))),0,RIGHT(H10,LEN(H10)-FIND(".",H10)))))<=2) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Complex Q
Hi Sean,
Am Sat, 2 Feb 2013 03:15:12 -0800 (PST) schrieb : 1) If value in K10 = "C/D", then (2) below does not apply 2) A value is already entered firstly in J10 3) The value being entered in H10 has a max of 2 decimal places, eg user can enter 1000 or 1000.12, but not 1000.123 try: =AND(OR(LEN(J10)0,K10="C/D"),LEN(H10)<=LEN(INT(H10))+3) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation - Complex Q
Bingo! Thanks works perfectly
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Question - XML Validation in Spreadsheet Web Component | Excel Programming | |||
Complex Data Validation | Excel Discussion (Misc queries) | |||
complex data validation | Excel Discussion (Misc queries) | |||
Complex Vlookup and List Validation and Nested IF statements | Excel Worksheet Functions | |||
complex validation for lottery numbers | Excel Discussion (Misc queries) |