Excel selective numbering

Question

i am doing an excel spreadsheet where i need the number "1" to be in the first 43 rows. then need the number 3 in the next 38 rows then the number 5 in the next 39 rows and then the number 6 in the next 39 rows. i want to create a formula for this so i can use this spreadsheet year after year. i know i could type each one in but i need a formula to create a more sustainable spreadsheet. these numbers are for regions and the amount of rows is how many stores per region. that's why i want it to be specific and i could edit one formula to fix it all. can anyone help?

Answer 1

nested if...

=if(row()<44,1,if(row()<82,3,if(row()<121,5,6)))

Answer 2

=Row()

This will give you the row number of the current cell. You can combine it with IF statements.

=IF(Row()<44,1,IF(Row()<82,3,6))

Answer 3

As you've mentioned your interest to use this spreadsheet year by year,in VBA you could use the following code in order to create a dynamic range of regions/stores.

On VBA, insert a module with the code below:

Sub region_loop()
n_region = InputBox("How many regions do you need?")

Cells(1, 1) = "Region"
For i = 1 To n_region
    n_stores = InputBox("How many stores does the region " & i & " has?")
    lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        For j = lrow + 1 To lrow + n_stores
            Cells(j, 1) = i
        Next j
Next i
End Sub