# Restricting text/number length on cell based on a condition

#### ban

##### New Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### ban

##### New Member
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.

Cheers

Replies
6
Views
366
Replies
3
Views
356
Replies
1
Views
142
Replies
19
Views
699
Replies
3
Views
222

1,195,632
Messages
6,010,788
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back