Same formula refrence off different tabs

Hi, I am struggling to refrence a formula off different tabs, so for example in Tab sheet1 I have a table but in A1 i have a formula =can1!C7 which refrences off sheet CAN1 and then in B1 i have the same formula but off CAN2 so it reads =CAN2!C7. How can I just drag down so that the CAN goes in sequence but the cell ref stays the same? C7

Hi,

In A1 and dragged down

=INDIRECT("'Can"&ROW()&"'!C7")

Hi,

Thanks but this doesnt seem to work. it shows #REF!. I have put it in exactly as you placed it. =INDIRECT("'Can"&ROW()&"'!C7") should I write anything after ROW(?) or just put it in exactly like that?

Did you put this in row 1 and copy down?

If not you'll need to deduct something from the ROW() function.

So for instance if you put the formula in row 10 you'll have to deduct 9 so that ROW()-9 will equal 1 (for the CAN1 tab)

So assuming it is in Row 10
=INDIRECT("'Can"&ROW()-9&"'!C7")

adjust as necessary if its in another row.