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 <michael.tiernan@gmail.com>
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: users+unsubscribe@global.libreoffice.org
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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.