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
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 

 

3 Replies

@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)

@Subodh_Tiwari_sktneer 

Hi, I am a complete novice when it comes to numbering and I'm so tired of being frustrated with something as simple as entering numbers 123..... I have to add names and sometimes delete... When I do this the numbering of course is off and I don't know how to correctly input the function and or sequence function to assist me. I have the Office product for MacBook ...I purchased MacBook student 2019.

Anyway I can get a tutorial for how to?

 

@IrisJ 

I would suggest you to open your own question by describing what exactly you are trying to achieve.

It would be helpful if you upload a sample file with the desired output on a different sheet mocked up manually to show the end result you are after.