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

Try

=SUMPRODUCT(('[Incidents
period0703.xls]DATA'!$G$8:$G$2000=$C8)*(CONCATENATE('[Incidents
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 end


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=561126

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


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=561126

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" wrote in message
...
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

In DataSubtotals uncheck that column in the Add Subtotal list.


--
HTH

Bob Phillips

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

"Danny Lewis" wrote in message
...
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