Function in LO Calc to return contents of cell addressed by string

[trueriver]

I am looking for a function something like

=CELL(“contents”,"$A$1")

where the string argument is not a constant but a string I build up to contain the desired address. This gives a #REF! error in version 6.4.7.2 on Linux.

The version =CELL(“contents”,$A$1)

works in the sense that it delivers the contents of cell A1, but does not work for what I want to do.

I actually want to calculate the address of the cell I am wanting, not reference a cell by a constant address.

Is this possible in LO? Do I need to upgrade my venerable version to do this? And if it is possible in any LO version, what is the function I would need to use please?

At a “meta” level, I have not posted much here and would also value comment on how well I am asking the question, including whether you feel I used appropriate tags? Feel free answer that “meta” question either by a reply here or by private message. I want to learn how to make it easy for you wizards to give me the help I sometimes need

[robleyd]

Perhaps you want the INDIRECT function:

Returns the reference specified by a text string.

[karolus]

@trueriver

but a string I build up to contain the desired address.

maybe you can shorten the whole task by something like:

=INDEX($A$1:$Z$100; calculated_row_number ; calculated_column_number )

[trueriver]

@karolus

maybe you can shorten the whole task by something like:

=INDEX($A$1:$Z$100;

Thank you for this suggestion, it is not the function I was looking for but for MOST use cases I have in mind it will, as you suggest, make the process simpler. Big thanks.

But I am marking the following as the solution because for some use cases INDEX would not seem to work (especially where the variable part of the address includes a variable sheetname)

@robleyd

Perhaps you want the INDIRECT function:

Big thanks to both contributors, both of you have helped me move forward.