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

=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"carrera" wrote in message
...
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

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.

"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"carrera" wrote in message
...
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

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

"carrera" wrote:

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.

"Bernard Liengme" wrote:

=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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"carrera" wrote in message
...
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