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]

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)

"Danny Lewis" <[email protected]> wrote in message
news:[email protected]...
> 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

[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)

"Danny Lewis" <[email protected]> wrote in message
news:[email protected]...
> 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