 # 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 :

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.

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 column`D` you need to rewrite the argument of `INDRECT()` to `"\$Daily.D"...` manually!

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 ?