Return relative position based on combination of two cells

[Antoddio]

I've been learning more lately and using index, match, indirect, etc. with more success. I've struggled to find an answer for this one, although I am sure it is very easy. Here is the issue. I am trying to set a formula so that the data on the "cover sheet" tab will be populated from the "Bad Debt" tab that is in yellow. The formulas can't be hardcoded because the number of rows will change each time the report is run because customers are added or dropped off. However the "comp codes" won't change and the relative position of the totals in relation to the detail won't change.

My thought was for example to have excel find a match for "2000COMPANY TOTAL" (in this example it is cell D26&D27) and then return the value 3 rows over and 6 down from that match in order to populate the "Specific Provision" item. Then with the same "2000COMPANY TOTAL" match populate the 3 rows over and 7 rows down for the value under "AR Current", etc. etc. Then also do the same for "3000COMPANY TOTAL". I hope you are getting the idea here. I've tried if and offset functions, but couldn't get it to check the whole column for a match.

The idea would to be able to paste a new information in the Bad Debt tab and have it automatically populate the cover sheet.

The file is attached, your help is much appreciated!!

[jjhayes]

Here is the approach I tried...using sumifs. It required a small modification to summary page (adding a row above for the allowance %). For example, in AR Current on Cover sheet for Company Code 2000, I used this formula:

Please Login or Register  to view this content.

, where d$1 is the .05% allowance. It adds up the totals and subtracts the credit amounts and then multiplies by your allowance amount. For the groups that have more than one column to sum from (for example, 31-90 picks up both 31-60 and 61-90) I added two sumifs together.

Take a look at the attached to see if it makes sense.

[JohnTopley]

In "Cover Sheet" C5

=IFERROR(OFFSET('Bad Debt'!$G$2,MATCH('Cover Sheet '!$A5,'Bad Debt'!$D$2:$D$200,0)+COUNTIF('Bad Debt'!$D$2:$D$200,$A5)+COLUMNS($A:C)+2,,1,1),0)

Copy across and down

NOTE: I changed the column A "Company Codes" to TEXT to match those in "Bad Debt")