0

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. I want the Sl. No. field to be automatically populated based on entry in flag field.

I am confused as to finding the search up from a cell to find first instance of “m” or “s” in the flag field; subsequently extract the value in Col. A of corresponding flag field where “m” has occurred and incrementing it by one. Any help would be appreciated.

Note: Any deletion or addition of rows should cause the sl. no. field to change number automatically depending on the flag.

enter image description here

Community
  • 1
  • 1
Melko
  • 41
  • 7

2 Answers2

0

In the first cell that has the SI Number (cell A3) put this formula:

=IF(D3="s", INDEX($A:$A, ROW()-1)+0.01, 1+ROUNDDOWN(INDEX($A:$A, ROW()-1), 0))

Copy this formula downwards in the A column.

Explanation:

The idea is to check the letter in the D column in the same row. If it is an "s" then take the value from the cell above it and add 0.01 to it. If it is not an "s" (an "m"), then also take that value from the cell above it, but remove the decimals from it and add 1 to it.

To survive from row deletions, the reference to the cell above is not done with a plain A2 relative reference, but with INDEX($A:$A, ROW()-1). This takes the current row number minus one, and retrieves the cell in that row.

Follow-up question

To make the counter restart again when the D column has neither an "m" or an "s", nest another IF in the formula:

=IF(D3="s", INDEX($A:$A, ROW()-1)+0.01, IF(D3="m", 1+ROUNDDOWN(INDEX($A:$A, ROW()-1), 0), 0))

This will produce the value 0 when the D column does not have either "m" or "s". To hide this value, format the A column with 0.00;-0.00;;@.

Community
  • 1
  • 1
trincot
  • 317,000
  • 35
  • 244
  • 286
  • trincot, this is what I was looking for. I overlooked the power of “roundup” function for this case. As an extension to this question, can we have a condition, wherein, if Description, Rate and Flag are not empty, it would automatically fill the serial number? I will have different headings and I can assign flag “h” to heading also so that the numbering gets restarted when it encounters “h”. The link to sample image is as follows http://s000.tinyupload.com/?file_id=55770715542589349164 – Melko Dec 16 '17 at 11:03
  • This formula will through a #VALUE error if the value above it, in column A, isn't numeric. – Variatus Dec 16 '17 at 11:11
  • Variatus, I do not plan to enter any value in Col. A manually. Hence, it is felt that “text entry” would not be a realistic case. – Melko Dec 16 '17 at 11:25
  • I added an answer for the follow-up question concerning "h". – trincot Dec 16 '17 at 11:31
  • Trincot, the formula works as desired. Also applied the formatting to sl. No. field. It does not show the zero. Could you briefly explain the syntax 0.00;-0.00;;@? – Melko Dec 17 '17 at 10:07
  • That syntax provides four formats separated by a semi-colon: (1) for positive numbers: `0.00` = show two decimals. (2) for negative numbers: `-0.00` = prefix with minus symbol. (3) for zero: nothing = show nothing :-) (4) for text: ´@` show the text as-is. Read [more about these formats](http://www.mbaexcel.com/excel/how-to-use-excel-custom-number-formatting/). – trincot Dec 17 '17 at 17:46
  • @trincot If there is a blank cell above a certain Sl. No., the formula the Sl. No. For the next item is not automatically returned. Eg. 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. – Melko Feb 02 '18 at 09:53
0

I suggest this formula for your column A

=(1/IF(LEN($D11),1,100))+SUM(INDIRECT(ADDRESS(ROW()-1,COLUMN())))

and this formula for your column B

=IF(LEN($D11),"","Sub-" & ROUND(MOD($A11*100,100),0) &" of ") & "Item " & INT($A11)

Please enter the formulas in row 11 and copy up and down as required. Note that the sub-items need not be marked in column D. The formula looks for any entry with a length of more than 0. You might use "m" or anything else. If you wish to be less flexible, change LEN($D11) to $D11="m" in either formula or both.

Format column A like 0.00. You might colour the main items red by using conditional formatting based on the length of $B11. If it's less than 10 it should be red.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • My formula for column A will restart numbering after the cell above it, in column A, is either empty or contains text. – Variatus Dec 16 '17 at 11:09
  • Using your formula, I am getting the following 1.00 2.00 3.00 3.01 4.01 5.01 6.01 7.01 8.01 9.01 10.01 The desired output is 1.00 1.01 1.02 1.03 2.00 2.01 2.02 3.00 4.00 4.01 4.02 – Melko Dec 16 '17 at 11:23
  • Also, the text in col. B not necessarily be “item” / “sub-item”. The descriptions were indicative only. – Melko Dec 16 '17 at 11:28
  • My formula is incapable of delivering the result you have without your help. Remember to mark only "Main" item rows. "Sub" item rows are identified by not being marked (cell in column D must be blank and empty). – Variatus Dec 17 '17 at 02:48