One way:

=AVERAGE(INDIRECT("'Sheet 2'!A1:A3"))



In article ,
"Craig Spencer UK" <Craig Spencer wrote:

I am trying to set up a spreadsheet that has a summary sheet collating
information from other worksheets in the same workbook. However, whenever I
insert a column in the other worksheets the formula changes to track the
original value, rather than staying with the same cell. I need it to always
refer to the same cell in order to get an average of the 3 most recent
entries on that worksheet. How d I do this?