Ask Your Question
0

Customize offset

asked 2018-05-20 12:37:29 +0100

unknown1001 gravatar image

Hi,

I have offset in many cells but have the same direction i would like to change the offset in all cells same time

=SUM(OFFSET(Mapa.B1;6105-6;0;6;1))

i create cell C20 with reference value Mapa.B1 and change reference to:

=SUM(OFFSET(C20;6105-6;0;6;1))

but its not working i try the same with rows in cell C20 i put row number and change the formula to:

=SUM(OFFSET(Mapa.B1;C20-6;0;6;1))

not working, how i can easily change all offsets from all cells?

edit retag flag offensive close merge delete

Comments

What do you mean by not working? Is there an error or just an unexpected SUM result?

SM_Riga gravatar imageSM_Riga ( 2018-05-20 16:10:46 +0100 )edit

i need one cell to control offset reference parameter for other one but when i put as reference value cell address its give me a 0 output

=SUM(OFFSET(Mapa.B1;6105-6;0;6;1))

gives me output 1

but cell C20 as reference for formula

=SUM(OFFSET(Mapa.B1;C20-6;0;6;1))
unknown1001 gravatar imageunknown1001 ( 2018-05-20 16:31:36 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-05-20 16:29:22 +0100

updated 2018-05-20 16:30:01 +0100

Hi there!

Regarding your first non-working example, you need to use INDIRECT function to tell Calc that the contents of C20 is a textual reference to cell address. So the correct formula should be SUM(OFFSET(INDIRECT(C20);6105-6;0;6;1)) Please read more here - https://help.libreoffice.org/Calc/Spr...

Please give more information on the second case - if C20 cell contains number and the new reference is within a valid range, the formula should be working.

edit flag offensive delete link more

Comments

second one is working but i think will be better first one with reference -6;0;6;1 for sum last 6 cells in column. Thank you!

unknown1001 gravatar imageunknown1001 ( 2018-05-20 17:01:19 +0100 )edit

as C20 can be value like Mapa.B6000 ? or only numbers?

unknown1001 gravatar imageunknown1001 ( 2018-05-20 17:03:29 +0100 )edit

If you are using your C20 cell as a reference to start point for OFFSET function (the first argument in OFFSET) - yes, you can use text reference in C20 cell combined with INDIRECT, as my answer suggests. You just write Sheet.Cell address as a text, and INDIRECT converts it to the reference Calc understands.

SM_Riga gravatar imageSM_Riga ( 2018-05-20 17:11:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-20 12:37:29 +0100

Seen: 49 times

Last updated: May 20 '18