Does ADDRESS function in Calc work within other functions?

I have not been using LO Calc very long, so this is probably a dumb question. I have been trying to use spreadsheet functions to complete a project for someone and I need to have Calc functions reference another cell from the cell that the formula is in. For example, I can use =OFFSET(A4,-1,5) to get a value stored 1 row up and 5 columns over from cell A4.

I can use =(ADDRESS(ROW(),COLUMN(),4)) to return the cell address that the function is in.

However, I can’t use the ADDRESS function within the OFFSET function to get the current cell the OFFSET function is in to get the same result, such as =OFFSET((ADDRESS(ROW(),COLUMN(),4)),-1,5).

Does ADDRESS not play well with others, or am I doing something wrong?

Peter

Please consider to read this thread concerning the parameter delimiter.
And: Any function should also work if called as a sub-expression from a parameter position of an enclosing function. If one doesn’t, it’s either a bug, or a very special case I would like to get reported.

To do what you are asking, INDIRECT is required: ADDRESS result isn't accepted as parameter.

However, normally standard cell references are best, as answered here and here. That is, simply use:

=F3

Or perhaps you want this in cell A4:

=OFFSET(A4,-1,5)

When either of these formulas are filled down or copied, the references will automatically be changed for the new location.

Note: To prevent it from adjusting, write it as =$F$3 instead.

To use ADRESS() (correctly applying INDIRECT as already explained by @jimk ) together with OFFSET is clearly a detour.

Assuming the cell to get the formula is K13 (e.g.). Using =OFFSET(K13;-1;+5) with the relative address of the current cell the formula should do the same, and also behave the same way if copied / filled elsewhere.

But that’s still a strange detour: =P12, again used in K13 with the relative address would do it the easy (and less error-prone) way.

Dare I guess you had to ask the question because you wanted to emulate a reference in the R1C1-style reading =R[-1]C[5]? Don’t worry. Relative addressing is standard in LibrOffice. Simply choose the target for one example cell and make sure the cell address doesn’t contain a “$”. It will behave relative then under Copy / Fill.

Explanation:
Understanding the rectified original formula =OFFSET(INDIRECT(ADDRESS(ROW();COLUMN();4));-1;5) would result in the reference to the cell one row above and 5 columns to the right of the current cell which is the same as you get with the above.

Edit 2018-03-21 regarding the fourth comment on the thanks by @PAlotta:

As announced there I make this attachment.

Thank you Jim K and Lupp, you both gave me a clearer understanding of ADDRESS vs. INDIRECT. Jim K, the link was exactly what was needed. I usually know what I need but can’t phrase the question right to find the answer.
I can’t use standard cell references because the OFFSET function needs to be used in a VLOOKUP formula that needs to be copied to a large number of cells (more of my wanting to reduce repetitive editing/copying of the same formula block to get this project done).

Thanks again,

Peter

Quoting @PAlotta: “… because the OFFSET function needs to be used in a VLOOKUP formula…”
For this you will need to use the fourth and the fifth parameter of OFFSET, but neither ADDRESS nor INDIRECT. The construct INDIRECT(ADDRESS(ROW(); COLUMN();...)) cannot do more than to return a simple reference to the current cell whether inside or outside of a call to OFFSET() and whether used with VLOOKUP() or otherwise.
=VLOOKUP($A$1;OFFSET(K13;-1;5;100;4);4;1) is a well working example.

If you actually replace the cell reference like K13 (or$K$13 or similar) with a variable reference: Why do you think to need that? Why not apply the variable modifications to the second and third parameter of OFFSET?

Quoting lupp "For this you will need to use the fourth and the fifth parameter of OFFSET, but neither ADDRESS nor INDIRECT. "

This is typical of one of the VLOOKUPs:

=VLOOKUP((OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,5)),$‘Another_Sheet’.$A$3:$‘Another_Sheet’.$J$202,9,0)

I am using OFFSET to supply the SearchCriterion for VLOOKUP to reference a value at the end of a formatted row. If I don’t use INDIRECT, how can I give OFFSET a way to find the SearchCriterion value using height/width?

You may want to study the example I attach to my answer by editing.
If my demonstration seems irrelevant to you, you should read this thread in a another forum.