Indirect Funtion reference not updating

[Ronanm]

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?

 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

 

[XOR LX]

Hi,

See if my article here helps:

https://excelxor.com/2016/01/08/incrementing-indirect-column-references-within-sumifscountifs/

and give me a shout here if you're struggling to adapt the solution given there to your set-up.

Regards

 

[Ronanm]

Hi XOR LX

Thanks for that. That was an impressive article, but I got a little lost half way down.

I got as far as

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&names&"'!D:D"),,COLUMNS(C:$D)),INDIRECT("'"&names&"'!E$3:E$24"),""))

and that returns a #VALUE!

The file is at https://www.dropbox.com/s/lbh6lnyuhmcye4b/ResourceSheet_Example_email.xlsx?dl=0 and the new formula above is in Cell E3, with the original formula I had in cells F3, G3 etc...

Regards

Ronan

 

[Aladin Akyurek]

It seems that E$3:E$24 must change to F$3:F$24, etc. when you copy the formula. Assuming that D3:D24 is fixed, try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&names&"'!D3:D24"),$D3,INDIRECT("'"&names&"'!"&CELL("address",E$3:E$24))))

 

[Ronanm]

Many thanks for the formula Aladin, it was just what was needed. To be honest I've not used CELL("address", before, so that's something I will be looking into. But great, perfect, works a treat.

Thanks

Ronan

 

[Aladin Akyurek]

Many thanks for the formula Aladin, it was just what was needed. To be honest I've not used CELL("address", before, so that's something I will be looking into. But great, perfect, works a treat.

Thanks

Ronan

You are welcome.