Dragging formulas with sheet names
 

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

Dragging formulas with sheet names
 

=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

Dragging formulas with sheet names
 

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

Dragging formulas with sheet names
 

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