Restricting text/number length on cell based on a condition

[ban]

Hi everyone

I'm trying to set up a spreadsheet where I can restrict what the users enter in a certain column based on a criteria in another column.

For example, the condition in Column A asks whether the costs are project related or not (only Y/N can be used) - based on the entry, if the costs are not project related then the user has to enter a cost center number (5 digits in length) and if the costs are project related then the user has to enter in the project number (either 10 or 12 digits in length).

Is this possible to do using data validation?

Thank you in advance for all you replies.

Cheers
ban

 

[Andrew Poulsom]

You can use a Custom Formula in Data Validation:

Excel Data Validation Examples Custom Criteria

 

[ban]

Hi Andrew

Thanks for looking into my query, I've tried using a custom formula in the data validation as you've suggested. I've grouped the conditions based on 3 possible entries; N (5 digits, E (10 digits) and I (12 digits)

In data validation I've used "Allow" as Whole number and "Data" as between, the custom formulas I've used as the minimum and maximum numbers are:
Minimum: =IF(A1="N",2000,IF(A1="E",1000000000,IF(A1="I",100000000000)))
Maximum: =IF(A1="N",2999,IF(A1="E",1999999999,IF(A1="I",199999999999)))

I then tested this but for some reason it only works for the first condition N, it doesn't seem to let me enter in a 10 or 12 digit number if E or I is selected in column A.
Can you advise what I might be doing wrong please?

Cheers