How do I quickly duplicate a formula but from different worksheets?

Please Login or Register  to view this content.

Above is a formula that is pulled from sheet 1 of a different file. The formula is in one cell, but I want to copy the formula from sheet 2,3,4,5,etc.
I tried to pull the formulated cell down the column to copy the formula hoping that the sequence would begin auto populating but it did not. Does anyone know how to populate the formula down a column without having to manually go back and change 1' to 2' and 1' to 3' and 1' to 4', etc?
Thank you,
BW

You will have to use INDIRECT:

e.g.

=INDIRECT("'[Loan Commission Report.xls]"&ROWS($A$1:$A1)&"'!$G$43")

but that will only work with open workbooks...

if they have to be closed, then you can download the Morefunc.xll addin and use INDIRECT.EXT instead of INDIRECT.

http://download.cnet.com/Morefunc/30...-10423159.html