Formula for data validation for entry series number only

Hello Guys,

Kindly help me creating a formula for Data Validation that can enter only a series number.

Example if in cell A1 = 1000. The only data can be entered in cell A2 SHOULD BE plus 1 or 1001. The number 1002, 1003, 1004, or 1000 or 999 and others should be restricted. Even if I am going to directly entered in A3 up to A100 SHOULD BE plus 1 from the last serial number which is 1001.

Thanks in advance.

Originally Posted by shivya

1. Enter 1000 in A1
2. select A2
3. Go to Data Validation
4. Select Whole number
5. In formula, type =A1+1
6. Copy validation and paste in other cells as well by selecting validation in paste special.

Hi it is working when I enter in the immediate next cell. However, it is not applicable if I will skip a cell (skip cell A2) and I enter in A3. Ex. A1 = 1000, i go directly to A3 , the data validation is not working. the actual cell range is A1 - A10. if A1 is 1000, and i go directly to A6 its hould have 1001. Another instance, if A1 is 1000, A2 is 1001 and i go directly to A7 it should 1002. Can you possibly do this. Thanks.

Yeah its working they cannot leave the blank cell. The problem is, there are instances that they need to skip a blank cell and a need to proceed to the next cell and when they do in the next cell it supposed to be in a series number. Please help. thanks in advance. Ex. A1 = 1000. cell A2 need to be skipped. So, they need to go to A3. A3 = 1001.

Enter your starting value in A1

In A2 use =IF(ISBLANK(B2),"",MAX($A$1:A1)+1)

Adjust cell references if you are starting in other than A1

Thank you so much. I think this fine for me MAX($A$1:A1)+1. it can leave blank but still in series.