Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Define chart range using indirect reference | Charts and Charting in Excel | |||
INDIRECT - Strange behavior | Excel Discussion (Misc queries) | |||
Copying a formulae down a column that includes an INDIRECT | Excel Discussion (Misc queries) | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions |