INDIRECT Follow On

[Danny Lewis]

Following on (hopefully you can help me Ardus)

I can't get it to work in context

If in cell AF3 is 0703

currently the formula below is
=SUMPRODUCT(('[Incidents period0703.xls]DATA'!$G$8:$G$2000=$C8)*('[Incidents
period0703.xls]DATA'!$W$8:$W$2000="Mainline"))

but how can I change this formula to depend on the contents of AF3?

ie if AF3 was 0704 the formula would read
=SUMPRODUCT(('[Incidents period0704.xls]DATA'!$G$8:$G$2000=$C8)*('[Incidents
period0704.xls]DATA'!$W$8:$W$2000="Mainline"))

Many thanks in advance

 

[Special-K]

Try

=SUMPRODUCT(('[Incident
period0703.xls]DATA'!$G$8:$G$2000=$C8)*(CONCATENATE('[Incident
period',RIGHT(TEXT(10000+AF3),4),'.xls]DATA'!$W$8:$W$2000="Mainline"))

Note your Incident Period files must all have 4 digits at the en

 

[Special-K]

Could try this

=SUMPRODUCT((CONCATENATE("[Incidents
period",RIGHT(TEXT(10000+AF3,0),4),".xls]DATA")!$G$8:$G$2000=$C8)*((CONCATENATE("[Incidents
period",RIGHT(TEXT(10000+AF3,0),4),".xls]DATA")!$W$8:$W$2000="Mainline"))

I hope Im right this time

 

[Bob Phillips]

INDIRECT won't work with a closed workbook, so if you close the other
workbook, it will then fail.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

In Data>Subtotals uncheck that column in the Add Subtotal list.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)