CODEWITHSUNDEEP
excel
rhughes
rhughes

Reputation: 9583

Row Numbers in Excel Table

I have the following basic table:

1|
2|    Title....
3|
4|
5|    | Row Index | Type | Etc... |
6|    | 1         | abc  | .....  |
7|    | 2         | def  | .....  |
8|    | 3         | ghi  | .....  |
9|    | 4         | jkl  | .....  |

Note that the table does not start on Excel row 1. Technically ROW()-5 would work, but I do not want to hardcode the actual row the table starts on.

My formula for Row Index is:

=ROW()-CELL("row")+1

This works fine, except for when you edit another cell in the table. It seems that the formula assumes the row you edit is index 0 and starts the row count from there.

For instance, if I were to edit a cell in row 3 in the above table, the Row Index values would look like this:

| Row Index | Type | Etc... |
| -1        | abc  | .....  |
| 0         | def  | .....  |
| 1         | ghi  | .....  |
| 2         | jkl  | .....  |

After each edit, I think have to re-edit a cell in the top row to get the Row Index values correct again.

Is there a reliable way to display row numbers in a table?

Upvotes: 2

Views: 17034

Answers (3)

David Segovia
David Segovia

Reputation: 31

Let's say your table header starts on A5. Your first table row will have this formula:

=IFERROR(A5 + 1, 1)

After that, you can drag it through all rows.

Upvotes: 0

Slai
Slai

Reputation: 22876

If it is an actual Excel Table (Insert tab > Table or Home tab > Format as Table):

=ROW()-ROW([#Headers])

or

=ROW()-ROW(Table1)+1

Otherwise, you can use the absolute address:

=ROW()-ROW($5:$5)

Upvotes: 12

kennytm
kennytm

Reputation: 523214

Remove the CELL("row") and just use the formula

=ROW() - 5

The ROW function returns the row number of the cell containing the formula, which is what you want.

The CELL function, on the other hand, returns information about the last changed cell, which is why you see the strange behavior.

CELL(info_type, [reference])

  • Reference   Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. ...

Even if CELL returns information about the current cell, what you would get from ROW() - CELL("Row", <current_cell>) + 1 would be the constant 1 because the two functions cancel each other.

Upvotes: 1

Related Questions