Ask Your Question

Does ADDRESS function in Calc work within other functions? [closed]

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

edit retag reopen merge delete

Comments

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.

( 2018-03-19 15:25:06 +0100 )edit

3 Answers

Sort by » oldest newest most voted

To do what you are asking, INDIRECT is required: https://ask.libreoffice.org/en/questi....

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.

more

To use ADRESS() (correctly applying INDIRECT as already explained by @Jim K ) 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. more 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 more Comments 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. ( 2018-03-20 01:56:03 +0100 )edit 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? ( 2018-03-20 02:00:01 +0100 )edit 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?

( 2018-03-21 22:04:41 +0100 )edit

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.

( 2018-03-21 23:28:41 +0100 )edit

Stats

Asked: 2018-03-19 03:09:11 +0100

Seen: 1,563 times

Last updated: Mar 21 '18