How do you reference a cell in relation to a third cell?

CivFan · July 21, 2020, 3:29am

I’m looking for something that can do this pseudo-formula:

=F(27-1)+1

In other words, don’t reference cell F27, but always the one above it. So in case a new row is inserted above it, it would automatically change to:

=F(28-1)+1

jimk · July 21, 2020, 6:35pm

From the comment by @keme:

INDIRECT() is the obvious choice for address indirection, but OFFSET() is often easier to use. In many cases they are equally applicable to a problem.

OFFSET(F27;-1;0)+1

Documentation: OFFSET

CivFan · July 21, 2020, 8:56pm

Yeah this is much simpler for my case. Thanks, @keme! (And @jim-k)

CivFan · July 21, 2020, 3:40am

I figured it out. It’s a combo of the functions, ADDRESS, INDIRECT, ROW, and COLUMN.

=INDIRECT(ADDRESS(ROW(F27)-1, COLUMN(F27),4))+1

keme · July 21, 2020, 6:56am

That will do. INDIRECT() is the obvious choice for address indirection, but OFFSET() is often easier to use. In many cases they are equally applicable to a problem.

OFFSET(F27;-1;0)+1

(Note by @jimk: Like the allegory of the long spoons, other people can edit or delete your comment. If you want something to be changed, add another comment explaining what you want done.)

CivFan · July 21, 2020, 4:25pm

Oh nice! Can you add it as an answer?

keme · July 21, 2020, 4:41pm

Comment is not editable/deletable anymore, and superfluous to have it in two places.

Anyway, the existing answer with comment provides the “full picture”, so I’d rather keep it as is.

jimk · July 21, 2020, 6:33pm

superfluous to have it in two places.

No, the consensus is that it should be added as an answer as well. See Answer or comment, which one do I use? - #8 by jimk.

Also, comments can still be edited, as shown above.