Restart numbering based on a value in another cell

[mehtapaarth]

Hello,
I am using an excel formula to enter a number based on a value in another cell. If the value in the other cell is zero, I need to restart numbering.

A B C D E F G H I J K L M N O P Q E S
1 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
2 7 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2
3 4
4 5


This is the formula I am using:

=IF(($A$22-(D21*$A$21))>0,1,IF($A$23-((OFFSET(D21,0,(COLUMN($D$21)-COLUMN(D$21)))*$A$21))>0,2,IF($A$23-((OFFSET(D21,0,(COLUMN($D$21)-COLUMN(D$21)))*$A$21))>0,3,"")))


Cell K2 and on-wards should have "3" for "4" in cell A3 (A2 = 7, hence "1" repeats 6 times from B2:G2).

I know what is happening here. I am using an OFFSET formula. The part (COLUMN($D$21)-COLUMN(D$21)) is referencing the same cell and hence I keep on getting "2".

Is there a better way to address this?

Thank you

[djapigo]

Hi mehtapaarth,

It might just be me, but I can't see to follow your request... in your formula, you are referring to cells A22, D21, etc. which I have no reference to...

Can you please attach a sample spreadsheet with non-sensitive info for us to look at?

Thank you,
Dennis