Any way to enforce incremental numbering in columns?

[MrSteve]

Hello

In my excel spreadsheet, the first column will always contain either a number or nothing.

So, it would look like this...

Column
[ 1 ]
[ ]
[ 2 ]
[ 3 ]
[ ]
[ 4 ]

Is there any way to enfore the numbering is incremental? This means something like the following should never be allowed -


Column
[ 1 ]
[ ]
[ 3 ]
[ 2 ]
[ ]
[ 4 ]

(note I swapped the 2 and 3)

Can this be done?

Any help appreciated.

Thanks!

Steve

[Bob Phillips]

u could use Data Validation in A2 on with a custom type and a formula of

=A2=MAX($A$1:$A1)+1

[mikerickson]

Is 1,2,4,5.. allowed? (skipped numbers?)

[MrSteve]

Hello

Thank you for the replies.

No skipped numbers are allowed (sorry, should have mentioned that!)

Would anyone know a step by step way I can implement this?

Thanks so much

[Bob Phillips]

Just select A2:An, and goto Data>Validation, select an Allow type of custom, and then add the formula I gave.

A slightly better formula is

=AND(COUNTIF(A:A,A2)=1,A2=MAX($A$1:$A1)+1)

[MrSteve]

Works perfectly

Thank you very much for your help.

[MrSteve]

Hello

I was wondering if I could extend this a bit?

At the moment I have now forced incremental numbering. It is working perfect.

Is there any way I could provide a drop down list with the next number the only option in the list?

So if the cells look like -

[ 1 ]
[ 2 ]
[ ]
[ ]

If the user clicked in the third or fourth cell a drop down list would be displayed with the number 3 in it.

Thanks so much for your help

Steve

[Bob Phillips]

You could add a formula in a helper cell, C1 say, of

=MAX(A:A)+1

and use DV in the cells starting at A1 with an allow type of List and a formula of

=IF(COUNTA($A2:$A$30)=0,$C$1,"")

[MrSteve]

Thanks xld.

I tried that but it doesn't appear to be working?

I have attached the excel spreadsheet I am using.

Column A is where the numbers are and L1 is where the helper formula is.

Would you mind taking a quick look?

Thank you very much for your assitance.

[Bob Phillips]

Four things:

The cell L1 is formatted as text, so no formula will work there. Change it to General, and recalculate the formula (seleect L1, F2, Enter)

Because the helper cell is L1, you have to change the reference in the DV to that cell, so the $C$1 becomes $L$1.

In my narrative I said starting at A1. You are starting at A2, so the firts cell in the DV formula has to chnage from ($A2 to $A3.

I said an Allow type of List, you have left it as Custom.

[mdmackillop]

A different methodology. This will change any value entered to the next number.

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then
Target = Application.Max(Range(Cells(1, 1), Target.Offset(-1))) + 1
End If
End Sub

[/vba]

[MrSteve]

Four things:

The cell L1 is formatted as text, so no formula will work there. Change it to General, and recalculate the formula (seleect L1, F2, Enter)

Because the helper cell is L1, you have to change the reference in the DV to that cell, so the $C$1 becomes $L$1.

In my narrative I said starting at A1. You are starting at A2, so the firts cell in the DV formula has to chnage from ($A2 to $A3.

I said an Allow type of List, you have left it as Custom.

DOH!

Thanks again for your help. It is much appreciated.