Hi
I am using the Indirect function (which works nicely) to Sum three separate tabs on a summary tab. The problem is that when I drag the formula to the other rows and columns in the summary tab, the cell reference doesn't change, even though there are no $ signs to make it absolute.
So below, where it says "!D$3:D$24")" and also "'!E$3:E$24")))... It keeps looking at Column D or Column E as I fill the formula across to the other columns. Any ideas?
Thanks
Ronan
I am using the Indirect function (which works nicely) to Sum three separate tabs on a summary tab. The problem is that when I drag the formula to the other rows and columns in the summary tab, the cell reference doesn't change, even though there are no $ signs to make it absolute.
So below, where it says "!D$3:D$24")" and also "'!E$3:E$24")))... It keeps looking at Column D or Column E as I fill the formula across to the other columns. Any ideas?
Code:
IF(SUMPRODUCT(SUMIF(INDIRECT("'"&names&"'!D$3:D$24"),$D3,INDIRECT("'"&names&"'!E$3:E$24")))=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&names&"'!D$3:D$24"),$D3,INDIRECT("'"&names&"'!E$3:E$24"))))
Thanks
Ronan