Does ADDRESS function in Calc work within other functions?

[PAlotta]

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

[Lupp]

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.

[jimk]

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.

[Lupp]

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.

[PAlotta]

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

[Lupp]

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.

[Lupp]

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?

[PAlotta]

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?

[Lupp]

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.