restart numbering based on formula

melvinkoshy

New Member
Joined
Dec 13, 2017
Messages
27
I have set up a sheet with automatic numbering based on formula based on “flag” field.

Flag “h” is for headings

Flag “”m” and “s” are for Sl. No. field
If flag is “m”, then the Sl. No corresponding to that item is incremented by 1
If flag is “s”, then the Sl. No is corresponding to that item is indicated as main item number + 0.01

Flag “tot” is for Amount field which sums the value by looking up from “h” up to the “tot”

The issue which I am facing is that, for the first item under the next heading, the Sl. No. does not restart based on the flag. It numbers continuously from the last number under the heading above. In the example shown, D21 cell shall indicate 1.00 instead of 4.00.

The formula should return the 1.00 in the Sl. No. field if “h” is encountered i.e. I wish to ignore the any blank row(s) above a particular row (row without any flag). The Sl. No. shall restart for items under the subsequent new headings also. Please assist in correcting the code.

The working file is attached here https://ufile.io/vimtj

Note:
The formula in Cell D21 is
Code:
  =IF(B21="","",IF(B21="m",COUNTIF($B$2:B21,"m"),LOOKUP(10^35,$D$1:D19)+0.01))
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
If I'm reading all of your requirements correctly, then you want something like this:
Code:
=IF($B21="","",IF($B21="h",1,IF($B21="m",COUNTIF(INDEX($B$2:$B21,MAX(INDEX(ROW($B$2:$B21)*($B$2:$B21="h"),))-ROW($B$2:$B21)+1):$B21,"m")+1,IF($B21="s",INDEX($D$2:$D20,MAX(INDEX(ROW($B$2:$B20)*($B$2:$B20<>""),))-ROW($B$2:$B20)+1)+0.01,IF($B21="tot","get total","unknown")))))
 
Upvote 0
I copied this formula in B21 but to no avail

My concern is only about the Sl. No. field. It does not restart on the instance of encountering “h” in the flag field which is for heading.
Under each heading, the numbering should restart. With the formula

=IF(B21="","",IF(B21="m",COUNTIF($B$2:B21,"m"),LOOKUP(10^35,$D$1:D19)+0.01)) ,the numbering does no restart
 
Upvote 0
Please take a minute to read the forum rules, especially regarding cross-posting, and then add links here to your threads in other forums. Thanks.
 
Upvote 0
I apologise for the cross posting. I was not aware of the regulations. I am grateful to the solution provided by @JeteMc in the following link where I cross-posted.

https://www.excelforum.com/excel-ge...t-of-numbering-based-on-code.html#post4864049

This formula has enabled me to get the desired solution. I am posting it for the benefit of the community. Once again, I am sorry for the infraction of cross-posting.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top