CODEWITHSUNDEEP
openoffice.orgopenoffice-calc
Adam Baranyai
Adam Baranyai

Reputation: 3867

Getting the row number of a hyperlinked cell in open calc

I have a file with two spreadsheets, Sheet1 and Sheet2.

In Sheet1 I have cells that link to a specific cell in Sheet2. This is done, using OpenCalc's HYPERLINK function.

Let's assume I use the following function in Sheet1.A1:

=HYPERLINK("#Sheet2.D4","SomeText")

In Sheet1.A2 I would like to fetch the row number of the linked cell (Sheet2.D4), so the end result to be 4(as the 4th row from Sheet2 is linked)

How can I achieve this?

Thanks for the help!

Upvotes: 0

Views: 114

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

If this is only for =HYPERLINK() formulas for such internal cell links, then it can be achieved with this formula:

=MID(FORMULA(A1),SEARCH("[0-9]+(?!.*\.)",FORMULA(A1)),SEARCH("[;,]",FORMULA(A1))-SEARCH("[0-9]+(?!.*\.)",FORMULA(A1))-1)

More universal would be a macro solution.

Greetings

Axel

Upvotes: 1

Related Questions