Reference a cell of the previous row in the same table in Excel?

How can I reference the cell of

the Color column,
in the previous row,
of the same table,

from a cell of the same table? Without relying on the absolute row / column numbers of the worksheet if possible.

Like [[Color],-1] something...

SuperDuck

Posted 2013-06-29T09:32:23.180

Reputation: 612

Answers

If you want to use the structured reference format, try

=OFFSET([@Colour],-1,0)

You can also do this without using structured references. For example, in row 2 of the table (the first data row), enter

=D1

... if "Colour" is in column D. (Adjust to your situation). The latter will be much faster in large datasets, since Offset() is volatile and will recalculate whenever ANY cell in the workbook changes. That can greatly impact performance.

teylyn

Posted 2013-06-29T09:32:23.180

Reputation: 19 551

I almost always create a separate column in my table named "I" for index, which is numbered 1,2,3,4... Then to get the previous value use INDEX([Colour],[@I]-1).

You can also number it the column starting from 0 and adjust the formula accordingly, since you almost always access the row before.

The volatile OFFSET function causes my large workbooks to crash so I recommend to avoid at all costs.

user3575270

Posted 2013-06-29T09:32:23.180

Reputation: 21

How do you build the index in a table that gets new data all the time? Manually? Or with a formula? The latter would be catch 22, right? – teylyn – 2019-01-13T00:56:05.737

@teylyn I normally do it manually. This is perferred for me since when I sort data indexes do not change. I am sure you can get creative using formulas depending on your application e.g. use something like =ROW()-2 – user3575270 – 2019-01-29T19:13:40.383

1If you use a formula to create the index, then refer to that index in another formula, you may as well nest the formula for the index into the second formula. – teylyn – 2019-01-29T23:14:27.350

You could use ROW() - ROW([#Headers]) - 1. – Solomon Ucko – 2019-11-21T18:24:19.073

I know this is an old post but just like I needed something similar now, i'm sure this may be of help for someone later. If what you need is to do a running sum on a table column, for example:

Column 1 has the "income" data (positive and negative values)
Column 2 is the "balance" of the account

For each row, you need to add the previous balance (Column 2, previous row) and the income (Column 1, current row) to calculate the current balance.

One way to do that is using this formula in the Column 2, based on teylyn's answer:

=SUM([@Column1];OFFSET([@[Column2]];-1;0))

This will handle some errors occurring in the first row

PUAR

Posted 2013-06-29T09:32:23.180

Reputation: 21