EXCEL Auto number rows until value in cell

Question

It's probably extremely simple, but I'm having trouble making it work.

I need a formula that will automatically number rows based on the value in B1. I want to automatically number rows starting at 1 down to the value specified in B1 starting from column B1.

This must be a formula rather than using VBA due to additional technical challenges that it cannot overcome.

I succeed up until the defined value (blank), after which the subsequent row returns #VALUE due to the calculation's failure.

I have tried every combination of IF, IFS, >"", =, >=, AND, and OR that comes to mind.

What should I define in order to prevent the #VALUE error? The defined value in B1 will fall within the ranges of 6 and 48.

Answer 1

You can utilize SEQUENCE if you have Excel 365:

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

Because the values will flow down, there must be sufficient vacant cells below the formula cell.

This will produce a numbered sequence, where the number of rows is determined by a value in B1 and the first column is numbered 1.