currently, I am manually giving all numbers by skipping the middle title row.
is there any way I can assign an auto increment number if added new row is in any section?
CodePudding user response：
use in A5:
=INDEX(IF(B5:B="",,COUNTIFS(B5:B, "<>", ROW(B5:B), "<="&ROW(B5:B))))
CodePudding user response：
Add a hidden column with the last number and a formula to increment it in column A. Let's choose column C to be hidden. In A5, use this:
=IF(B5="",,C4 1)
In C5, use:
=IF(A5="",C4,A5)
Copy those cells to the rest of their columns. If any text is included in column B (starting from B5), the next number appears in column A. Column C repeats the new number if it appears, or the number of above (to be used as reference for column A in next row).
The result is like that:
| A | B | C |
|---|---|---|
| 1 | d | 1 |
| 1 | ||
| 2 | bv | 2 |
| 3 | bg | 3 |
| 4 | asd | 4 |
| 4 | ||
| 5 | vf | 5 |
| 5 | ||
| 5 | ||
| 5 | ||
| 5 | ||
| 5 | ||
| 5 |
Select column C and the option "Hide column" to don't be bothered by this column. If you need to make it visible again, click on one of the arrows that appear between columns B and D. The final result is like that:
| A | B | D | E | F |
|---|---|---|---|---|
| 1 | d | |||
| 2 | bv | |||
| 3 | bg | |||
| 4 | asd | |||
| 5 | vf | |||
See example here: https://docs.google.com/spreadsheets/d/1_IR0YOCpis6Yn_UrO9y_bx02haRQnEgrMhJl0QAltts