changing sheet reference to INDIRECT (3 references in formula)

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
To clarify. i'm using Office 365 if it's not seen via my profile to the left.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,977
Office Version
  1. 365
Platform
  1. Windows
Shouldn't the 2nd PaymentDue be DueDate?
Also if you make those ranges workbook scope, you can get rid of the indirect.
 
Solution

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

sheet 2 represents different amortization tables
 

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
Got it working! thanks. Can't believe I missed that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,977
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top