Sequential numbers that change when deleted.

%3CLINGO-SUB%20id%3D%22lingo-sub-1513981%22%20slang%3D%22en-US%22%3ESequential%20numbers%20that%20change%20when%20deleted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513981%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20of%20making%20automatic%20counting%20down%20a%20column.%20I%20am%20comfortable%20to%20input%20progressive%20numbers.%20i.e.%201-36%20down%20a%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20is%20there%20a%20formula%20that%20will%20edit%20following%20numbers%20if%20I%20deleted%20a%20number%20in%20the%20sequence.%20for%20example%2C%20If%20I%20have%20the%20numbers%201-36%20down%20a%20column%2C%20but%20wanted%20to%20delete%20the%20number%2015%2C%20then%20the%20'15'%20cell%20will%20be%20left%20empty%2C%20and%20the%20cell%20below%20would%20take%20up%20that%2015%2C%20and%20continue%20the%20counting%20from%20there.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20this%20for%20registers%20and%20not%20all%20pupils%20are%20booked%20onto%20the%20camp%20for%20each%20day.%20Ultimately%20I%20want%20the%20bottom%20number%20in%20that%20column%20to%20give%20me%20the%20total%20number%20of%20booked%20on%20players%20for%20each%20day.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3BRob%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1513981%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1514045%22%20slang%3D%22en-US%22%3ERe%3A%20Sequential%20numbers%20that%20change%20when%20deleted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1514045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625983%22%20target%3D%22_blank%22%3E%40robglaysher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20insert%20the%20sequential%20numbers%20in%20the%20range%20A1%3AA36%2C%20input%201%20manually%20in%20the%20cell%20A1%20and%20then%20place%20the%20following%20formula%20in%20A2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMAX(A%241%3AA1)%2B1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20you%20delete%20any%20number%20in%20between%2C%20the%20next%20number%20will%20be%20changed%20accordingly%20i.e.%20the%20sequential%20numbers%20will%20ignore%20the%20blank%20cells%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sequential%20Numbers.jpg%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204564iB6DD7BA0CF404896%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Sequential%20Numbers.jpg%22%20alt%3D%22Sequential%20Numbers.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20may%20try%20this%20formula%20in%20A37%20which%20will%20give%20you%20the%20count%20of%20numbers%20entered%20in%20the%20range%20A1%3AA36%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNT(A1%3AA36)%20%3C%2FSTRONG%3E(%20this%20will%20count%20the%20numbers%20entered%20in%20the%20range%20A1%3AA36)%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTA(A1%3AA36)%20%3C%2FSTRONG%3E(this%20will%20count%20the%20cells%20which%20have%20either%20text%20of%20numbers%20in%20them%20in%20the%20range%20A1%3AA36)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all, 

 

Is there a way of making automatic counting down a column. I am comfortable to input progressive numbers. i.e. 1-36 down a column.

 

But is there a formula that will edit following numbers if I deleted a number in the sequence. for example, If I have the numbers 1-36 down a column, but wanted to delete the number 15, then the '15' cell will be left empty, and the cell below would take up that 15, and continue the counting from there. 

 

I am using this for registers and not all pupils are booked onto the camp for each day. Ultimately I want the bottom number in that column to give me the total number of booked on players for each day. 

 

Many thanks,

 Rob 

 

1 Reply
Highlighted

@robglaysher 

If you want to insert the sequential numbers in the range A1:A36, input 1 manually in the cell A1 and then place the following formula in A2

=MAX(A$1:A1)+1

and then copy it down.

 

So if you delete any number in between, the next number will be changed accordingly i.e. the sequential numbers will ignore the blank cells like this...

 

Sequential Numbers.jpg

 

Or you may try this formula in A37 which will give you the count of numbers entered in the range A1:A36

=COUNT(A1:A36) ( this will count the numbers entered in the range A1:A36)

or

=COUNTA(A1:A36) (this will count the cells which have either text of numbers in them in the range A1:A36)