Ask Your Question
0

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

asked 2020-07-21 05:29:37 +0100

CivFan gravatar image

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
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2020-07-21 20:35:15 +0100

Jim K gravatar image

updated 2020-07-21 20:36:39 +0100

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

edit flag offensive delete link more

Comments

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

CivFan gravatar imageCivFan ( 2020-07-21 22:56:18 +0100 )edit
0

answered 2020-07-21 05:40:34 +0100

CivFan gravatar image

updated 2020-07-21 05:41:17 +0100

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
edit flag offensive delete link more

Comments

2

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 @Jim K: 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.)

keme gravatar imagekeme ( 2020-07-21 08:56:14 +0100 )edit

Oh nice! Can you add it as an answer?

CivFan gravatar imageCivFan ( 2020-07-21 18:25:21 +0100 )edit

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.

keme gravatar imagekeme ( 2020-07-21 18:41:19 +0100 )edit
1

superfluous to have it in two places.

No, the consensus is that it should be added as an answer as well. See https://ask.libreoffice.org/en/questi....

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

Jim K gravatar imageJim K ( 2020-07-21 20:33:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-21 05:29:37 +0100

Seen: 57 times

Last updated: Jul 21 '20