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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,279
Messages
5,571,284
Members
412,375
Latest member
BRJoeyMelo
Top