I have a sheet with Sl. No. (Col. A) Description(Col. B) and Rate(Col. C). I have included a flag field where I will enter m for main item and s for sub-item. The Sl. No. field is automatically populated based on entry in flag field. (see the attached image)
I have used the formula for Col. A
=IF(D3="s", INDEX($A:$A, ROW()-1)+0.01, 1+ROUNDDOWN(INDEX($A:$A, ROW()-1), 0))
The issue I am facing is that if there is a blank cell above a certain Sl. No., the formula the Sl. No. For the next item is not automatically returned. e.g. If A5 = 1.02 and A6 is blank, then A7 does not automatically populate the serial number based on the flag s or m in the flag field.
How is the formula to be tweaked to achieve that the blank cells above a certain Sl. No. is ignored and the first instance of the number above Sl. No. field is considered and incremented as per the flag.
Attachment 559646
Bookmarks