Suppose I have a table like below, and a reference at c8=a1
How can I get e8 to display 20, so it can be reused based on the content of a5?
| A | B | C | ||
|---|---|---|---|---|
| 1 | orange | 20 | ||
| 2 | blue | 40 | ||
| 3 |
c8=a1
e8=[some formula] (c8)
now e8 displays “20” (the content of b1)
It might be XY problem, but I think if I can extract the row number of “a1” from c8, I can use it in indirect(“B”&[row from c8=1])
Try to express yourself more clearly.
Column designators in Calc are upper case (if not a locale unknown to me shows them otherwise).
With LibreOffice version 6.2 or higher you can actually get that row number (as a string) in the simple case you described by
=REGEX(FORMULA(C8);"(?<=[A-Z])\d+";;1)
where the look-behind helps to get the formula working even if the content of C8 is (e.g.) = 3 + 19* A1.
Without the REGEX() function it’s a bit more complicated.
The chances that this will not work due to the fact that the formula isn’t actually as simple as your example suggests, is high,
Anyway you should analyze your sheet design for an occurrence of the XY-problem in a more fundamental sense. The need to get a reference based on a reference elsewhere should not occur in a well designed sheet. (Otherwise it should even be supported by standard functions.)
You shouild expect lots of problems.