Ask Your Question
1

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

asked 2018-03-19 03:09:11 +0200

PAlotta gravatar image

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-03-21 12:59:27.274183

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.

Lupp gravatar imageLupp ( 2018-03-19 15:25:06 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2018-03-19 15:17:15 +0200

Jim K gravatar image

updated 2018-03-19 15:22:05 +0200

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.

edit flag offensive delete link more
1

answered 2018-03-19 15:44:14 +0200

Lupp gravatar image

updated 2018-03-21 23:32:18 +0200

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.

edit flag offensive delete link more
0

answered 2018-03-20 01:28:15 +0200

PAlotta gravatar image

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

edit flag offensive delete link 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.

Lupp gravatar imageLupp ( 2018-03-20 01:56:03 +0200 )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?

Lupp gravatar imageLupp ( 2018-03-20 02:00:01 +0200 )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?

PAlotta gravatar imagePAlotta ( 2018-03-21 22:04:41 +0200 )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.

Lupp gravatar imageLupp ( 2018-03-21 23:28:41 +0200 )edit

Question Tools

1 follower

Stats

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

Seen: 472 times

Last updated: Mar 21 '18