autofill formulas - need worksheet reference to change automatically

[accola0003]

Hello:

I am working in Excel 2003. I have a worksheet designed to summarize data from previous worksheets in the same workbook. I am having trouble getting Excel to properly copy cell formulas as I expand the summary worksheet. Worksheet names are LOAN 1, LOAN 2, LOAN3, etc. Formulas in each column are as follows:

Column A =T('LOAN 1'!H49)
Column B =T('LOAN 2'!H49)
Column C =T('LOAN 3'!H49)


If I use Autofill or manuall copy and paste the formula in Column C to Column D, Excel inserts the formula: =T('LOAN 3'!G49).

The formula I WANT in Column D is =T('LOAN 4'!H49).

In other words, I want the worksheet reference to change, not the cell reference.

I know how to fix the cell reference by changing it to "absolute", but is there a way to get Excel to automatically change the worksheet reference automatically as I expand (i.e. as I add Column E, Column F, Column G, etc.)?

This will take forever if I re-type the formulas manually for each new column. Thanks!

[arthurbr]

If you start in col A try
="T('LOAN "&column()&"'!$H$49)"

[NBVC]

Try:

=T(INDIRECT("'LOAN "&ROWS($A$1:$A1)&"'!H49"))

copied down.