Thankyou for the solution to my problem which you posted. In the end I went
with the pivot-table solution proposed for me elsewhere. However, I will be
keeping your code in hand for the future.

"Alan Beban" wrote in message
...
Garbin wrote:
Ok,

Original data is in sheet1, condensed data on sheet2...
Cell A1 on sheet2 contains a reference number carried forward from sheet1
Cell B1 on sheet2 contains the formula =AVERAGE(Sheet1!C2:C5)
Cell C1 on sheet2 contains the formula =AVERAGE(Sheet1!D2:D5)
Cell D1 on sheet2 contains the formula =AVERAGE(Sheet1!E2:E5)
and so on...
Cell B2 on sheet2 should have the formula =AVERAGE(Sheet1!C6:C9)
Cell C2 on sheet2 should have the formula =AVERAGE(Sheet1!D6:D9)
Cell D2 on sheet2 should have the formula =AVERAGE(Sheet1!E6:E9)
but actually reads (when cells in row 1 dragged to fill the cells
below)...
Cell B2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!C3:C6)
Cell C2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!D3:D6)
Cell D2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!E3:E6)

Ian


In B1 on sheet2

=AVERAGE(INDIRECT("Sheet1!C"&ROW(H$2)+4*(ROW(H1)-1)&":C"&ROW(H$2)+4*(ROW(H1)-1)+3))

In C1

=AVERAGE(INDIRECT("Sheet1!D"&ROW(H$2)+4*(ROW(H1)-1)&":D"&ROW(H$2)+4*(ROW(H1)-1)+3))

etc., copied down

Alan Beban