Cell to allow only 1 - 1000 in numeric order

[hajiali]

I want excel to limit A3:A1003 user to only input numbers 1-1000 for their choices form highest to lowest and if they go out of order to alert next value is the missing number

ex: if users input 1,2,3,4,5 and if users accidentally tries to input 7 as next value for excel to prompt message " Next choices needs to be 6"

 

[mole999]

can you not just add the numbers to start off, and only make them visible when they enter other text ?

 

[hajiali]

the number 1-1000 does not have to be in the cell order ex that a3 has to be 1 or a4 has to be 2.... the numbers 1 - 1000 can go in any cell in A3:A1003 so if users wants they can put 1 in any cell and 2 in any cell If that is do able. But i think i would need a VBA i might me wrong.

 

[jtakw]

Hi,

Select A3:A1003, DV, Custom, enter this formula:

=A3=COUNT(A$3:A$1003)

Customize "Input Message" and/or "Error Alert" if desired.

 

[hajiali]

Thanks for the reply Jtakw

I already have "=COUNTIF($A$3:$A$122,A4)=1" in my DV so that it does not allow a duplicate value. Is there a way that we can add multiple DV's. The other thing is that the excel will recognize the missing number and alert the user the next value. so the alert will let the user know that next chose.

 

[jtakw]

You don't need "=COUNTIF($A$3:$A$122,A4)=1", my DV formula as posted in Post # 4 will Only allow 1 to 1000, in ascending order, in Any cell within A$3:A$1003, and Not Allow duplicates.

Remove your DV formula and use Only mine.

 

[hajiali]

Jtakw i really appreciate it that works for both duplicates and in ascending order. now for the message in DV is there way to let user know what the next value for ex: if they input 1,2,3,4,5 and try 7 that the message says " please use choice number 6"

 

[jtakw]

As for "Input Message" and "Error Alert", I'm afraid that can Only be some Text of your design, we will Not be able to Alert the User what number to use next.

If that's what you want, then in Any Cell (Not within the range of A3:A1003), enter:

=COUNT(A$3:A$1003)+1

Then select A3:A1003, DV, "List", and point to the Cell where you entered the above formula for the "Source"

Now the "Drop Down" list for Any cell within A3:A1003 will Always let the User choose the Next ascending number from 1 to 1000.

EDIT: Now you can use something like "Please use value from Drop Down" in your "Input Message" and/or "Error Alert"

 

[hajiali]

ok I guess thats one way to go about this good thought. However is there a way to do this in a VBA?

 

[jtakw]

I don't see a reason to do this with VBA while there's a simple solution using Native Excel functions.

If you must use VBA, I can't help.