Search code examples
excelexcel-formulanumberssequentialautonumber

EXCEL: Auto number rows until value in cell

This is probably very easy, but I can't seem to get it to work.

I'd like a formula for autonumbering rows based on the value in B1. Starting from B1 id like to autonumber rows from 1 and down until the value defined in B1.

This need to be a formula, and not VBA beacuse of other technical obstacles cannot solve.

I manage until I reach the defined value (blank) is returned, and the next row returns #VALUE because the calculation fails.

I have tried all the IF, IFS, <>"", <=, >=, AND, OR combinations I can think of.

How can I define this and avoid the #VALUE error. The defined value in B1 will be a value ranging from 6 to 48

screendump of result

Would really appreciate help on this.

Solution

  • If you have Excel 365 you can use SEQUENCE:

    Put =SEQUENCE(B1,1,1,1) in the cell below Month

    The values will spill down - that means there have to be enough empty cells below the formula cell

    This will create a numbered sequence --> number of rows is set via value in B1, one column, starting at 1