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?
What do you mean by not working? Is there an error or just an unexpected SUM result?
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))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 - Spreadsheet Functions - LibreOffice Help
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.
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!
as C20 can be value like Mapa.B6000 ? or only numbers?
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.