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
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: