changing sheet reference to INDIRECT (3 references in formula)

[MurphDog]

The formula I'm having trouble with on sheet1 It does the following...
1. Pulls the year from cell A1
2. Row 6 (column headers) has each month listed representing each month (M6:X6)
3. Column I represents sheet names. In the example below, the sheet name in I7 is "sheet2"

Sheet2 is an amortization table.
1. Column C is the Payment Due
2. Column B is the Due Date (every 2 weeks)
3. That total is what the formula pulls for each month, ie, twice a year there are 3 payments, all other months have 2 payments.

On Sheet1, below each month is the formula below (where M = January, N=February, etc). It works fine but when I try to replace sheet2 with an INDIRECT function, it doesn't give me the correct value

This would be the formula cell M7 (January) that works....

=SUMIFS('sheet2'!$C$20:$C$400,'sheet2'!$B$20:$B$400,">="&1&M$6&$A$1,'sheet2'!$B$20:$B$400,"<"&1+EOMONTH(--1&M$6&$A$1,0))

I tried using a cell range and a defined name. I get the same incorrect result.

=SUMIFS(INDIRECT("'"&$I7&"'!PaymentDue"),INDIRECT("'"&$I7&"'!DueDate"),">="&1&M$6&$A$1,INDIRECT("'"&$I7&"'!PaymentDue"),"<"&1+EOMONTH(--1&M$6&$A$1,0))

=SUMIFS(INDIRECT("'"&$I7&"'!$C$20:$C$400"),INDIRECT("'"&$I7&"'!$B$20:$B$400"),">="&1&M$6&$A$1,INDIRECT("'"&$I7&"'!$C$20:$C$400"),"<"&1+EOMONTH(--1&M$6&$A$1,0))

What am I missing?

Thanks,
Murph

 

[MurphDog]

To clarify. i'm using Office 365 if it's not seen via my profile to the left.

 

[Fluff]

Shouldn't the 2nd PaymentDue be DueDate?
Also if you make those ranges workbook scope, you can get rid of the indirect.

 

[MurphDog]

Shouldn't the 2nd PaymentDue be DueDate?
Also if you make those ranges workbook scope, you can get rid of the indirect.

Well, in reality, sheet1 represents a year, and i'm going to have multiple years.. Verifying your first question...

 

[MurphDog]

sheet 2 represents different amortization tables

 

[MurphDog]

Shouldn't the 2nd PaymentDue be DueDate?
Also if you make those ranges workbook scope, you can get rid of the indirect.

You are correct, Checking to see if I just mistyped that in this thread or if that is the mistake in my workbook.

 

[MurphDog]

Got it working! thanks. Can't believe I missed that.

 

[Fluff]

You're welcome & thanks for the feedback.