Quote Originally Posted by excellicious
Richard- you're heavensent!!! U 2 Dave.

It works great. Thank you so much. Could you explain to me your formula in layman term?
Hi Dave,

Glad to be able to help.
With the code:
=OFFSET('FINAL REPORT'!$A$1,MATCH($A$3,'FINAL REPORT'!$A:$A,FALSE)-1+ROW()-3,2)
The middle bit MATCH($A$3,'FINAL REPORT'!$A:$A,FALSE) finds the A3 value (the label 'System') in the Final Report column A. This returns the value 113 since the label SYSTEM is in A113.

Now we use the OFFSET() function to find the cell which is offset from a cell datum. In this case the datum cell is A1 on the final report. The general arrangement of the OFFSET() function is
OFFSET(datum cell, row offset, column offset)
(There's actually a couple of optional extra parameters to specify a height and width, but they're not needed here).

So starting with the A1 cell, we offset by 113 (the Match() function) which takes us to A114, and hence we need to deduct the -1 to stay with A113. Since we're going to put this formula in C3 on the Grand Total sheet and copy it down, we also want something that will increase the row offset by 1 as we copy it down through C4:C9. Therefore we use the ROW() function which returns the row number of the cell in which the ROW() function is placed. In this case this returns 3, since we're in cell C3. But because for C3 we don't want any further offset for this starting cell, we deduct the constant '3'. As we copy it down to C4, the Row() function will return '4' from which we deduct 3, so we have 1 to add to the offset of 113 to give 114. And in C5 the additional offset is 2, (5-3) ....etc.....

Finally the OFFSET() function concludes with the column offset, which in the case of the Item Count field is 2. i.e. two columns to the right of the Column A datum. And for the Dollar Amount field, obviously the offset is 3.

Hope this makes sense.

Incidentally, there's a small typo in your workbook. Stationery is spelt 'ery' not 'ary'

Rgds