CODEWITHSUNDEEP
libreofficelibreoffice-calc
user1587451
user1587451

Reputation: 1008

How to reach the end of a chain of cells

I have a file with two sheets:

sheet_A

  A        B
1 Mr. Joe  USD

sheet_B

A              B
1 =sheet_A.A1 ???

sheet_B.B1 shall show the value USD. I know i could get it easily with =sheet_A.B1 but I do not want that.

If I enter into sheet_B.B1 =ADDRESS(ROW();COLUMN()-1) I get the output $C$1 and with =INDIRECT(ADDRESS(ROW();COLUMN()-1)) Mr. Joe.

How can I "navigate" through a chain sheet_B.B1 - sheet_B.A1 - sheet_A.A1 - sheet_A.B1?

Edit 1

Maybe I need something like this

=OFFSET(FORMULA(ADDRESS(ROW();COLUMN()-1);0;1)#

sheet_B.B2 shall show the content of sheet_A.B2 in relation of the value in sheet_B.A1

Upvotes: 0

Views: 23

Answers (1)

Jim K
Jim K

Reputation: 13790

Here are two possibilities. Either formula produces USD in sheet_B.B1.

=INDIRECT(ADDRESS(ROW();COLUMN();;;"sheet_A"))
=VLOOKUP(A1;$sheet_A.A1:B1;2)

Documentation: ADDRESS, VLOOKUP.

EDIT:

One more idea: The following produces the string "=sheet_A.A1", which could be parsed for the sheet name and cell address. Perhaps you would like to use it to refer to sheet_A.B1.

=FORMULA(INDIRECT(ADDRESS(ROW();COLUMN()-1)))

However, as I commented, there is probably an easier way for what you are trying to accomplish.

Documentation: FORMULA.

EDIT 2:

Here is the formula you requested. It uses REPLACE to remove = at the beginning of the string.

=OFFSET(INDIRECT(REPLACE(FORMULA(INDIRECT(ADDRESS(ROW();COLUMN()-1)));1;1;""));0;1)

Upvotes: 1

Related Questions