dynamic range for index/match formula

This formula below does an ok job but but it doesn't have the ability to identify the las row of the Final Report tab. I would manually have to adjust the "119", which defines the last row, in order for it to work. I did try to define the name range-=OFFSET(FINAL REPORT!$A$1,0,0,COUNTA(FINAL REPORT!$A$2 REPORT!$A:$A),1) but no luck.

=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,0)+1,":B700")),0),1)

TIA,
excellicious

Originally Posted by excellicious

This formula below does an ok job but but it doesn't have the ability to identify the las row of the Final Report tab. I would manually have to adjust the "119", which defines the last row, in order for it to work. I did try to define the name range-=OFFSET(FINAL REPORT!$A$1,0,0,COUNTA(FINAL REPORT!$A$2 REPORT!$A:$A),1) but no luck.

=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,0)+1,":B700")),0),1)

TIA,
excellicious

It looks like the bit inside the OFFSET() part of the composite dynamic range name function is awry. The general form is:

=OFFSET($A$1,0,0,COUNT($A:$A),1)

What are the references to Final Report! and Report! ? It appears you're trying to define a 3D range.

Rgds

Thank you Richard and Dave for your replies.

Please see my attachment, my goal is to have column C & D of the SYSTEM section on the FINAL REPORT sheet to be transferred to the C & D columns of the Grand Total sheet. Once the data are downloaded to the FINAL REPORT sheet, the SYSTEM section last row varies from row 97-119. This is my problem so I would go to my formula to make adjustment manually.

Dave-this is quite sad but I did use the site that you referred in your email to build my formula but I keep getting #REF! for the result.

I defined and named "Afternoon"
=OFFSET('FINAL REPORT'!$A$1,0,0,COUNT('FINAL REPORT'!$A:$A),1).

and my formula is now
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,Afternoon,1)+MATCH($B3,INDIRECT
(CONCATENATE("'FINALREPORT'!B",MATCH($A$3,Afternoon,0)+1,":B700")),0)
,1)

Originally Posted by excellicious

Thank you Richard and Dave for your replies.

Please see my attachment, my goal is to have column C & D of the SYSTEM section on the FINAL REPORT sheet to be transferred to the C & D columns of the Grand Total sheet. Once the data are downloaded to the FINAL REPORT sheet, the SYSTEM section last row varies from row 97-119. This is my problem so I would go to my formula to make adjustment manually.

Dave-this is quite sad but I did use the site that you referred in your email to build my formula but I keep getting #REF! for the result.

I defined and named "Afternoon"
=OFFSET('FINAL REPORT'!$A$1,0,0,COUNT('FINAL REPORT'!$A:$A),1).

and my formula is now
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,Afternoon,1)+MATCH($B3,INDIRECT
(CONCATENATE("'FINALREPORT'!B",MATCH($A$3,Afternoon,0)+1,":B700")),0)
,1)

Hi,

I think I'd be inclined to use the slightly easier formula as follows: C3 on the Grand Total Sheet

=OFFSET('FINAL REPORT'!$A$1,MATCH($A$3,'FINAL REPORT'!$A:$A,FALSE)-1+ROW()-3,2)

and in D3

=OFFSET('FINAL REPORT'!$A$1,MATCH($A$3,'FINAL REPORT'!$A:$A,FALSE)-1+ROW()-3,3)

Copy both of those down to row 9.

HTH

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