So, using your spreadsheet example, here is what I came up with:
The formula figures out where the values are in column "A" and the
uses "X" to offset the value to return the word.
This is how it looks:
Row | Col A | Col B | Col C
n | One | Num X | {Magic formula}
n+1 | Two | |
n+2 | Three | |
n+3 | Four | |
n+4 | Five | |
This is {Magic formula}
=INDIRECT(ADDRESS(ROW()-ROW($A$n)+$B$n+ROW(),COLUMN($A$n:$A$n),4,1),1)
(The actual row number "n" is put in the formula in four places.)
And this is what you get.
Row | Col A | Col B | Col C
n | One | 4 | Four
n+1 | Two | |
n+2 | Three | |
n+3 | Four | |
n+4 | Five | |
Broken down it says: (Starting from the inside)
ROW()-ROW($A$n)+$B$n+ROW()
Return the value of what row this cell is in (i.e. Col C, Row n)
then subtract the row where we're starting from (top row) Add in
the offset number you provide (Col B, Row n) and then add in the
row where we're working from (last 'row()' call.)
COLUMN($A$n:$A$n)
Then return a value for the columns we're working in (where the
letters are, "A")
,4,1)
(4 Means return the value as A8 instead of $A$8 and the 1 means
say it as A8 instead of R8CA)
ADDRESS( row, column, 4, 1 )
Now, treat all that as an address of a row and column.
INDIRECT( address from above, 1 )
Go get the *VALUE* at that address and display it
On Thu, Jul 11, 2024 at 12:39 PM Michael Tiernan <[email protected]>
wrote:
> I'd posted a piece about using indirect references on the Libreoffice
> forum.
> This is where it is:
>
> https://ask.libreoffice.org/t/how-can-i-get-the-current-cell-column-letter/39139/4?u=her_kitty_daddy
>
> The thing was how to get the values another cell points to.
>
> If you used that sort of solution with the column fixed as "A" and use the
> value in "C" to refernce the row involved.
>
> See if that leads you to a solution?
> --
> << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
>
> Non Impediti Ratione Cogatationis
> Women and cats will do as they please, and
> men and dogs should relax and get used to the idea. -Robert A. Heinlein
>
--
<< MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein
--
To unsubscribe e-mail to: [email protected]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
