Customize offset

calc

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

unknown1001
15 ●3 ●6 ●10

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 ( 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 ( 2018-05-20 16:31:36 +0100 )edit

add a comment

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 ( 2018-05-20 17:01:19 +0100 )edit

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

unknown1001 ( 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 ( 2018-05-20 17:11:12 +0100 )edit

add a comment

Customize offset

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

Customize offset edit

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

Customize offset