Copying Indirect formula so rows increment

[Peter Davison]

I have the following code using indirect so it can look at the sheet name from cell (DK$34)

=SUMIFS(INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ10:$MMZ10"),INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$3:$MMZ$3"),"="&DK$33,INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$1:$MMZ$1"),"="&$N$2)/DK$32

I need to be able to copy this formula down so the $AQ10:$MMZ10 increments by 1 each time.

I've looked at offsets and rows and row but still can't work it out.
Any help would be appreciated

 

[Toadstool]

Hi Peter Davison,

Try replacing the string with

CELL("address",$AQ10:$MMZ10)

 

[Peter Davison]

Which part of this address does the above replace?
"'[MasterData.xlsm]"&DK$34&"'!$AQ10:$MMZ10"

 

[Toadstool]

"'[MasterData.xlsm]"&DK$34&"'!"&CELL("address",$AQ10:$MMZ10)

 

[Peter Davison]

I changed it to this and it just gives me a #value result

=SUMIFS(INDIRECT("'[MasterData.xlsm]"&DK$34&"'!"&CELL("address",$AQ10:$MMZ10)),INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$3:$MMZ$3"),"="&DK$33,INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$1:$MMZ$1"),"="&$N$2)/DK$32

Any thoughts why

In its pre code -
=SUMIFS(INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ10:$MMZ10"),INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$3:$MMZ$3"),"="&DK$33,INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$1:$MMZ$1"),"="&$N$2)/DK$32

The result is 4372 which is correct.

Appreciate your help

 

[Toadstool]

Try this

MasterData.xlsm
	A	B	C
9	Original		Relative Row
10	4372		4372
11	4372		4919
12	4372		5465
13	4372		6012
14	4372		6558
Sheet3

Cell Formulas
Range	Formula
A10:A14	A10	=SUMIFS(INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ10:$MMZ10"),INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$3:$MMZ$3"),"="&DK$33,INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$1:$MMZ$1"),"="&$N$2)/DK$32
C10:C14	C10	=SUMIFS(INDIRECT("'[MasterData.xlsm]"&DK$34&"'!"&CELL("address",$AQ10)&":"&CELL("address",$MMZ10)),INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$3:$MMZ$3"),"="&DK$33,INDIRECT("'[MasterData.xlsm]"&DK$34&"'!$AQ$1:$MMZ$1"),"="&$N$2)/DK$32

 

[Peter Davison]

Perfect, thank you so much for all your help.

 

[Toadstool]

Perfect, thank you so much for all your help.

You're welcome.
Thanks for the feedback.