CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
ADBL
ADBL

Reputation: 29

Google spreadsheet relative row numbering + skip empty cell

Using Google spreadsheet, I need a column to show relative row numbering, so that:

  1. Spreadsheet rows can be moved around, and numbering stays relative (meaning that if I move row number 12 between rows 4 and 5, this row becomes number 5, previous 5 becomes 6, etc.)
  2. Using a condition based upon a cell value in a given row, the row numbering cell may be empty (or not); if empty, that row number is being skipped, and numbering takes back up in the next row where the condition is unmatched.

Here is an example:

| Row nbr |  B col. | [Explanation]                       |
|---------|:-------:|-------------------------------------|
| R01     | Value 1 |                                     |
| R02     | Value 1 |                                     |
| R03     | Value 2 |                                     |
|         | Value 3 | Col. B value = 3 => Col. A is empty |
| R04     | Value 2 | Numbering takes up                  |
| R05     | Value 1 |                                     |
|         | Value 3 | Col. B value = 3 => Col. A is empty |
| R06     | Value 2 | Numbering takes up                  |
| ETC.    |         |                                     |

Here, the condition (to skip numbering) would be "IF corresponding B cell = Value 3" (then skip numbering)

Right now, I'm using a formula that matches requirement #1 above:

=ROW(INDIRECT("A"&ROW()&":A"))-9

(The trailing "-9" is just an offset so numbering can start at 01 from row 10).

So basically, I need to adapt (or change) this code so that besides relative numbering, for row N, if corresponding B column cell value = XYZ, then A column cell is empty (vs. numbered): that row is being skipped, and numbering takes back up from N on the next row where the B cell value ≠ XYZ.

Here's a Google spreadsheet example.

Many TIA's.

Upvotes: 2

Views: 2334

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7894

I strongly recommend not to use ROW() with explicitly specified offset in formula because should you delete/add rows above your table, your will have to adjust formulas in all cells. Here's my solution (column A:A is row numbers, B:B - values, start adding formula in cell A10):

=IF($B10="Value 3","",ROWS($B$10:$B10)-COUNTIF($B$10:$B10,"Value 3"))

Or if you insist on relative rows (change 10 to your offset from top):

=IF(INDIRECT("B"&ROW())="Value 3","",ROWS(INDIRECT("B"&10&":B"&ROW()))-COUNTIF(INDIRECT("B"&10&":B"&ROW()),"Value 3"))

Upvotes: 1

Related Questions