PDA

View Full Version : Any way to enforce incremental numbering in columns?



MrSteve
01-23-2008, 10:26 AM
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
01-23-2008, 01:36 PM
u could use Data Validation in A2 on with a custom type and a formula of

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

mikerickson
01-23-2008, 01:40 PM
Is 1,2,4,5.. allowed? (skipped numbers?)

MrSteve
01-28-2008, 03:48 AM
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
01-28-2008, 04:22 AM
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
01-28-2008, 05:09 AM
Works perfectly :)

Thank you very much for your help.

MrSteve
02-11-2008, 03:18 AM
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
02-11-2008, 03:47 AM
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
02-11-2008, 07:54 AM
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
02-11-2008, 08:35 AM
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
02-11-2008, 08:43 AM
A different methodology. This will change any value entered to the next number.


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

MrSteve
02-18-2008, 07:56 AM
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.