Indirect, SumProduct and Errors

[fluffynicesheep]

Hi all,

I am currently using sumproduct to find out numbers in a particular column in a 2nd spreadsheet.

This formula works fine in column B:

=SUMPRODUCT(('Y:\National Careers Service - Mail Merge\Brentwood\[Brentwood.xlsm]a_Main'!$AR$1:$AR$10000=B4)+0,('Y:\National Careers Service - Mail Merge\Brentwood\[Brentwood.xlsm]a_Main'!$HL$1:$HL$10000>='Overview - CSO'!$B$5)+0,('Y:\National Careers Service - Mail Merge\Brentwood\[Brentwood.xlsm]a_Main'!$HL$1:$HL$10000<='Overview - CSO'!$B$9)+0)


However, I then have to redo this for Bromley, Chippenham etc - very time consuming!!

The town names are in column A, and I was hoping to automate this as much as possible using the Indirect function. - so the above would be linked to A1, then in the row down it would be linked to the town in A2 etc..

Even better would be if I could also add the =IFERROR function to return a 0 if the required worksheet wasn't found.

If you could point me in the right direction that would be great.

 

[DanteAmor]

Try:

=SUMPRODUCT((INDIRECT("'Y:\National Careers Service - Mail Merge" & A1 & "\[" & A1 & ".xlsm]a_Main'!$AR$1:$AR$10000")=B4)+0,(INDIRECT("'Y:\National Careers Service - Mail Merge" & A1 & "\[" & A1 & ".xlsm]a_Main'!$HL$1:$HL$10000")>='Overview - CSO'!$B$5)+0,(INDIRECT("'Y:\National Careers Service - Mail Merge" & A1 & "\[" & A1 & ".xlsm]a_Main'!$HL$1:$HL$10000")<='Overview - CSO'!$B$9)+0)

Note:
If you use INDIRECT to refer to external workbooks then they have to be open or else INDIRECT won’t work.

 

[fluffynicesheep]

Many thanks Dante Amor

That could be an issue then having to open multiple workbooks. Is there any way to do the same thing with the workbooks closed?

 

[DanteAmor]

The result is on your sheet, if the books are closed and you recalculate your sheet, then the #Ref error will appear

 

[fluffynicesheep]

Ok, so is there no way (or alternative formula) to do this with the external workbooks being closed?

 

[DanteAmor]

Another option would be to remove the formulas and update the values ​​with VBA, press a button or an event to update the values ​​in the sheet.