How To Customise Autofill for a Formula

[jja28]

I don't know if this is possible but what i'm looking to do is customis
the way autofill works when copying formulas.

I currently have a formula which references cells in other workbooks t
get an average:

=AVERAGE('[2006-02-03.xls]Point 2 B0'!$B$37,'[2006_02_10.xls]Point
B0'!$B$37,'[2006_02_17.xls]P2 B0'!$B$37)

All the sheets have the same layout so it's always the same Column an
row but when I drag down to autofill, it copies without changing th
row number. So for the example above all the formulas reference ro
37.

Is there a way I can get excel to sequentially reference the rows
possibly a way to actually edit the autofill tool?

I hope that's clear enough and that someone's got an answer (I'
running Excel 2003)

 

[Biff]

Hi!

Just remove the $ from the row references:

Make them $B37.

Biff

 

[Gord Dibben]

Try removing the $ signs from $B$37

B37 will increment as you drag the formula down the column.

See help on relative and absolute referencing.


Gord Dibben MS Excel MVP