How to reference a cell using a formula

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 :


So B1=+$Daily.C7, B2=+$Daily.C14 etc

Is this possible ?


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 (Correct answer mark) to the left of the answer that solves your question.


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.

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 ?