Formula to Reference Cell in Column Using @row

Min

Hello!

I'd like to be able to have a column formula where a cell is the same value as the next direct cell in another column. For instance, I'd like the cell on my third row under column "Successor Responsible" to equal the cell on my fourth row under column "Task Responsible".

Of course, the simplest way to do this is to have the formula "=[Task Responsible]3" but since I need to convert to column formula this is not currently an option.

Any help would be greatly appreciated thank you!

Paul Newcome

Insert an auto-number column (called "Auto" in this example) and a text/number column (called "Row"). Use this column formula in the Row column

=MATCH(Auto@row, Auto:Auto, 0)

Then in your Successor Responsible column you can use this column formula:

=IFERROR(INDEX([Task Responsible]:[Task Responsible], Row@row - 1), "")

Paul Newcome

Insert an auto-number column (called "Auto" in this example) and a text/number column (called "Row"). Use this column formula in the Row column

=MATCH(Auto@row, Auto:Auto, 0)

Then in your Successor Responsible column you can use this column formula:

=IFERROR(INDEX([Task Responsible]:[Task Responsible], Row@row - 1), "")

Min

I think that's very close! I'd like it so that if I type a name in the "Task Responsible", it would be input in the cell above in "Successor Responsible", not below. Currently my sheet looks like this:

But I would like successor "Kevin Lee" to be on the row where task responsible "Joshua Kim" is, if that makes sense.

Apologies if my wording was confusing!

Min

Actually I solved the issue myself, just changed the minus sign to a plus. Thanks Paul!

Paul Newcome

Sorry about that. I misunderstood what you were looking for. That's exactly it though. + 1 instead of - 1. Glad you were able to get it working. 👍️