Dragging formulas with sheet names

C

carrera

My sheets are named TM1, TM2, TM3 etc. to TM100

My formula reads ='TM1'!S$46

How can I drag the formula down so the subsequent formulas read....

='TM2'!S$46
='TM3'!S$46
etc.

Thanks
 
B

Bernard Liengme

=INDIRECT("TM"&ROW(A1)&"!S$46")
quotes on sheet name not needed when there are no spaces in name
but this add them =INDIRECT("'TM"&ROW(A1)&"'!S$46")
Each "' is " followed by '
no time to test, so try it out
best wishes
 
C

carrera

Hi
This just changed the A1 in the formula to A2, A3, etc.

I'm trying to have everything in the formula remain the same Except that I
need the first cell to be from the first sheet of the workbook (S46), the
cell below that to be the contents of S46 from the 2 sheet of the workbook,
the cell below that be the contents of S46 from the 3rd sheet of the
workbook, and so on.
 
C

carrera

Whoops, sorry...at first the formula wasn't working because I was referencing
it to the wrong part of my spreadsheet.

It DID work!

Thanks
 
Top