Another Indirect Issue (1 Viewer)

[IanT]

Hi

=SUMPRODUCT((A12:INDIRECT(Current!$AA$1&Current!$AA$3)))

I am using the above formula to sum a range the Current ref being the end point IE A20. This works great if the A12 is the current sheet, but if I use the formula below it errors, Previous being another Sheet. Can any one help!

=SUMPRODUCT((Previous!A12:INDIRECT(Current!$AA$1&Current!$AA$3)))

 

[Brianwarnock]

Firstly why are you using Sumproduct ? Sum will sum a range.

Secondly you have not said whether my previous response worked, I'll assume that it did and therefore that you have learnt nothing from it.

I have not used Indirect like this before and agree that excel help isn't helpful but a google search does reveal many sites, unfortunately none totally comprehensive.

Try

=sum(previous!A12:Indirect("previous!"&(indirect("current!$aa$1&c")&indirect("current!$aa$3"))))

I am assuming your addressing is correct, hope my () is

Brian

 

[IanT]

Hi Brian

Both formulas work great. I am using the sumproduct because I need to identify certain criteria from a data export, and the indirect allows me to identify year to date data from a previous year for comparison.

 

[Brianwarnock]

Following the post by NBVC on the other thread I think this should really be

=sum(previous!A12:Indirect("previous!"&current!$aa$1&c&current!$aa$3)))

Brian

 

[NBVC]

Brian,

I think you have an extra &c in there....

should it be:

=sum(previous!A12:Indirect("previous!"& current!$aa$1&current!$aa$3))

 

[Brianwarnock]

Yes
Sorry about the typo, well spotted.
It looks like I made an incorrect correction as it is an extra &c in front of &current

Brian