# Customize offset

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 close merge delete

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

( 2018-05-20 16:10:46 +0200 )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))

( 2018-05-20 16:31:36 +0200 )edit

Sort by » oldest newest most voted

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.

more

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!

( 2018-05-20 17:01:19 +0200 )edit

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

( 2018-05-20 17:03:29 +0200 )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.

( 2018-05-20 17:11:12 +0200 )edit