Excel MT 144 p1 Formula: Avoid Data Validation 1 Rule Limit

I'm trying to enter the following formula as a Custom Data Validation formula into cell F17, but it appears that the formula is too long to be entered:

Code:

=IF(ISBLANK(D17),F17="",AND(ISNUMBER(F17),OR(AND(ISNUMBER(B20),ISNUMBER(B21),F17+(G17/12)+(I17/12*0.5)<=B20,F17+(G17/12)-(I17/12*0.5)>=B21),AND(ISNUMBER(B39),ISNUMBER(B40),F17+(G17/12)+(I17/12*0.5)<=B39,F17+(G17/12)-(I17/12*0.5)>=B40),AND(ISNUMBER(B58),ISNUMBER(B59),F17+(G17/12)+(I17/12*0.5)<=B58,F17+(G17/12)-(I17/12*0.5)>=B59),AND(ISNUMBER(B77),ISNUMBER(B78),F17+(G17/12)+(I17/12*0.5)<=B77,F17+(G17/12)-(I17/12*0.5)>=B78),AND(ISNUMBER(B96),ISNUMBER(B97),F17+(G17/12)+(I17/12*0.5)<=B96,F17+(G17/12)-(I17/12*0.5)>=B97))))

I did a quick check, using the following string with 255 characters:

Code:

=12345678911234567892123456789312345678941234567895123456789612345678971234567898123456789912345678901234567891123456789212345678931234567894123456789512345678961234567897123456789812345678991234567890123456789112345678921234567893123456789412345678951234

It seems that Excel 2000 limits the # of characters in the Data Validation formula field to 255 characters, as it will not allow me to enter anything more than the above formula. Do newer versions of Excel allow for more characters?
Any ideas of how to shorten my formula and still get the same results?
Any help would be greatly appreciated!

Thanks,
-Brian

Hi all,

I need to setup a validation rule that would permit entries like those that follow:

- 8 digit number that MUST commence with 200 (i.e. 20012345, 20034342,
etc).

- The words "Free" , "Faulty" and "Employee"

I would the user to receive an error message is any of the above is not met, buit no need for a message before entering the data.

This rule will be applied to a single column (i.e. Column A)

Would have prefered to send you how far i've got with code, but not sure how to even start this one. Not even using the Excel 2007 Data Validation wizards.

Hope you can help,

Million thanks

D

Hi

In my table, I would like to set up a validation that does not allow a time entry to be inputted if it is earlier than the time noted above or beside it. For example -

For task "B", cell E3 completes its activity at 5:30 and task "C" commences its activity at 05:00 (cell D5) - this is a conflict as each subsequent row is a separate task that cannot commence before the prior one is completed.

Also cell D8 and cell E7 are in conflict - cell D8 cannot commence prior to the time in cell E7 (8:20). Also as you read right across each row, the times must be progressive. EG: cells C8 and B8 are in conflict. The "check" time in cell C8 has to be later than the time in cell B8 (6:55).

Just to complicate this a little more - there will be some rows missing as is the case in row 4.

Hoping someone can assist.

Steve


Excel 2007 A B C D E F 1 Arrive Check Commence Complete Depart 2 A 03:00 0-Jan 03:10 0-Jan 03:20 0-Jan 04:20 0-Jan 04:30 0-Jan 3 B 03:30 0-Jan 03:40 0-Jan 04:30 0-Jan 05:30 0-Jan 05:40 0-Jan 4 5 C 04:40 0-Jan 04:50 0-Jan 05:00 0-Jan 06:00 0-Jan 06:10 0-Jan 6 D 05:00 0-Jan 05:10 0-Jan 06:10 0-Jan 07:10 0-Jan 07:20 0-Jan 7 E 05:30 0-Jan 05:40 0-Jan 07:20 0-Jan 08:20 0-Jan 08:30 0-Jan 8 F 06:55 0-Jan 06:45 0-Jan 07:05 0-Jan 08:05 0-Jan 08:15 0-Jan Sheet1

I require a custom data validation formula to limit cell value to maximum of 4 decimal places.

0.0001 ok
0.02 ok
0.3 ok

0.12345 fail
0.123456 fail etc

Hi All,

Could you please help me with this:
I have a spreadsheet with list on invoices which is populated daily with new records. I need to be sure that each invoice is posted only once (in order to avoid duplicates). I've tried to do this by using a validation list on the Invoice No column with the function =COUNTIF($B$2:$B$2800,B2)

Hey all,

I have a spread sheet that I want to use data validation to only allow the user to enter a date in the format DD/MM/YYYY, if its not in that format then it displays the error message. the problem is that it will only let the user enter the format MM/DD/YYYY i tried to use the Custom section instead of the Date section but it still does the same. is there a way round it to get it to work?

Thanks

Tom

dear camarades
i have a date cell that i implemented in a data validation
but i dnt want who is inputing the data (if he enter 15 March in A1) to enter 3 March in A2..
how this can be solved?

Okay i am fairly new to Access so bare with me. I have created a split database for my dept at work.

I have two tables in my front end that are linked to the actual tables in the back end: [QA Route] and [Focus Techs]

I have created a split form called "Daily Route" that was created from the data in [QA Route].

Here is my question:

I have a column in [QA Route] named Install Tech and i have a column in [Focus Techs] called Focus Tech. In my form (Daily Route) i want to place a validation rule in a column called Focus Tech to do the following:

Return a "Y" if the data in [QA Route]![Install Tech] matches with a value in [Focus Techs]![Focus Tech].

Return a "N" if the data in [QA Route]![Install Tech] does not find a match with a value in [Focus Techs]![Focus Tech].

I hope this makes sense. I have tried using the expression builder, but haven't gotten anything to work.

Thanks in advance.

Hi
The code below works very well before I used copy & paste to copy a value(17) into one of the cells in A1:C13. When I copy & paste, an unvalid number (12) can be pasted into a cell without any error message, let alone undoing the paste. And what's more, after this copy & paste, all cells accept any values input. How can I avoid this copy and paste ruinning my data validation? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:C12")) Is Nothing Then
If Target.Value < 1 Or Target.Value > 12 Then
Application.EnableEvents = False
Target.Select: MsgBox "Invalid data": Target = ""
Application.EnableEvents = True
End If
End If
End Sub