SUM(OFFSET(INDIRECT) function doesnt work

[Darren OBeirne]

hi I am trying to establish a sum offset indirect function that I have seen done on another work book but I am getting ref errors. Can anyone explain the problem?
In the Loan sheet I create a reference column in column B and then other sheet has following formula
=SUM(OFFSET(INDIRECT($B$4),0,5,-12,1)) whereby B4 is the loan sheet name with a vlookup?

 

[Hui]

Darren

Firstly, Welcome to the Chandoo.org Forums

The issue is the -12
In =SUM(OFFSET(INDIRECT($B$4),0,5,-12,1))
-12 means 12 rows above the offset position,
But since you start at A10 on the Loan Worksheet with 0 offset, there isn't 12 rows to go up

If you change -12 to -10 you get
=SUM(OFFSET(INDIRECT($B$4),0,5,-10,1))
= 20,970.36
which is the Sum of Loan!F9:F10

 

[Darren OBeirne]

Darren

Firstly, Welcome to the Chandoo.org Forums

The issue is the -12
In =SUM(OFFSET(INDIRECT($B$4),0,5,-12,1))
-12 means 12 rows above the offset position,
But since you start at A10 on the Loan Worksheet with 0 offset, there isn't 12 rows to go up

If you change -12 to -10 you get
=SUM(OFFSET(INDIRECT($B$4),0,5,-10,1))
= 20,970.36
which is the Sum of Loan!F9:F10

you are a genius...love your work. This worked a treat. There was another more complicated formula too =SUM(OFFSET(INDIRECT($B$4),0,4,-(RIGHT($B$4,LEN($B$4)-14)-11),1)) but its probably a similar problem

 

[bosco_yip]

Please refer to the attached file.

Regards

 

[Darren OBeirne]

Please refer to the attached file.

Regards

thanks Bosco this works as well although I don't need 36mths and wonder how to relate your formula to total loan or interest payments.
I like this formula for it but cannot get it to work
Total Loan Repayments=SUM(OFFSET(INDIRECT($E$4),0,4,-(RIGHT($E$4,LEN($E$4)-14)-11),1))

 

[Darren OBeirne]

thanks Bosco this works as well although I don't need 36mths and wonder how to relate your formula to total loan or interest payments.
I like this formula for it but cannot get it to work
Total Loan Repayments=SUM(OFFSET(INDIRECT($E$4),0,4,-(RIGHT($E$4,LEN($E$4)-14)-11),1))

That should have said total loan repayments to date. The formula I have copied above is from another example and I am trying to get it to work.

 

[bosco_yip]

Maybe………...

total "Payment" paid to date.
=SUM(OFFSET(Loan!A$8,MATCH(B$3,Loan!A$9:A$48,0),3,-MATCH(B$3,Loan!A$9:A$48,0),))

Or,

total "Principal" paid to date.
=SUM(OFFSET(Loan!A$8,MATCH(B$3,Loan!A$9:A$48,0),4,-MATCH(B$3,Loan!A$9:A$48,0),))

Regards

 

[Darren OBeirne]

hello sorry to persist with this but I realise I have started with the wrong date lookup. Can anyone help with finding the following:

1. Find a date in a list and if it is there take this otherwise take the one immediately prior (earlier date)
2. Interest payments from the Indirect reference (date found at 1.) going back to 30th June prior year (not as my earlier question simply going back 12mths)

Thanks Darren