Hi all,
How do I reference a cell in another sheet using a formula.
On sheet 1 I have a daily finance record with a weekly total. On sheet 2 is weekly. On sheet 3 is monthly.
On sheet 2 I want to reference the total I have on sheet 1 of the weekly total. Column A has week count.
So I want to use something like :
B1=+$Daily.C(A*7)
So B1=+$Daily.C7, B2=+$Daily.C14 etc
Is this possible ?
TIA,
Zalid
Sorry for the delay in replying.
I’ve looked at your suggestions but none seem to work for me.
Is there a way to upload my spreadsheet so you can see it ?
It’s become a little more complicated now.
I need a way for the reference to be something like
E3=+$Daily.C4
E4=+$Daily.C(4+7) … So it takes the value 4 from the previous one and adds to it.
E5=+$Daily.C(11+7)
E6=+$Daily.C(18+7)
E7=+$Daily.C(25+7)
E10=+$Daily.C(32+9) … So it adds 9 this time before reverting to 7 for the next 3, and so on.
.
While in sheet 2, press =, click in the tab of sheet 1, click in the cell with the weekly total and press Enter.
See the resulting formula, and use it as a base for other calculations.
Add Answer is reserved for solutions.
Press edit below your question if you want to add more information; also can comment an answer.
Check the mark (
) to the left of the answer that solves your question.
Hello,
if you want to use a “calculated” reference, then you need function INDIRECT(), which allows to add the reference as a text string. According to your example:
Formula in cell B1: =INDIRECT("$Daily.C" & ROW()*7) (ROW() of cell B1 equals 1 here)
Formula in cell B2: =INDIRECT("$Daily.C" & ROW()*7) (ROW() of cell B2 equals 2 here)
… and so on
Attention: Function INDIRECT() does not work any longer (as desired) if you make changes to the range addressed by argument (which isn’t a reference but a text and hence not automatically updated). For example: If you add a column in sheet Daily before column C and you addressed data get shift to columnD you need to rewrite the argument of INDRECT() to "$Daily.D"... manually!
Ref.: LibreOffice Help - Function INDRECT
Hope that helps.
Instead INDIRECT, better use INDEX, it is not a volatile function and it’s a bit quicker and also can avoid handwrite text.
=index($Daily.$C:$C;ROW()*7)
Without seeing your sheet it is very hard to guess what you are really trying to do.
However you indicate that column A has a week count.
This suggests to me you are already trying to index your data in some way that independent of the row number.
I suggest you investigate the functions VLOOKUP and HLOOKUP.
Is there a way to upload my spreadsheet so you can see it ?