Absolute cell changes when I insert a row

MonBarks

New Member
Joined
Oct 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello Gurus,
I'm using this formula =SUMIFS('Weekly Invoices Record'!J5:J2045,'Weekly Invoices Record'!A5:A2045,'Create New Weekly Invoice '!$J$5:$J$5) which works fine until I insert a row in the Weekly Invoices Record sheet, then it starts looking on row 6, instead of 5, even though it was an absolute reference. I think I might need to use INDIRECT within my SUMIFS formula, but I just can't get my head around the syntax. Please help !!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,549
=SUMIFS( 'Weekly Invoices Record'!J5:J2045,'Weekly Invoices Record'!A5:A2045,INDIRECT("'Create New Weekly Invoice '!J"&ROW(A5)))
 

MonBarks

New Member
Joined
Oct 4, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
=SUMIFS( 'Weekly Invoices Record'!J5:J2045,'Weekly Invoices Record'!A5:A2045,INDIRECT("'Create New Weekly Invoice '!J"&ROW(A5)))

Thank you so much, it works perfectly. I was getting in such a pickle with "Indirect" all over the place :))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,138
Messages
5,570,394
Members
412,321
Latest member
Yusuf_A
Top