2 formulas reference same worksheet, one an array
I have a number of timesheets that I need data from in 2 different ways. I
created one formula on one spreadsheet and another formula on another spreadsheet to do this. The second formula is an array and the first is not. However, they both pull data from the same source. I am not sure if this is the cause of the problem or not, but I can not get both formulas to work at the same time. The first formula worked perfectly until I set up the second formula. Once I got the second formula to work by pressing ctrl+shift+enter, the first formula not longer worked. Any suggestions as to why this would be and how I can fix it? Here are the formulas in case those help. first formula: =IF(Q$2<"",SUMPRODUCT(--(INDIRECT("'"&Q$2&"'!$A$3:$A$50149")=$A$1),--(INDIRECT("'"&Q$2&"'!$C$3:$C$50149")=$A4),--(INDIRECT("'"&Q$2&"'!$D$3:$D$50149")=$B4),--(INDIRECT("'"&Q$2&"'!$E$3:$E$50149")=$C4),--(INDIRECT("'"&Q$2&"'!$F$3:$F$50149")=$D4),(INDIREC T("'"&Q$2&"'!$G$3:$G$50149"))),"") Second formula: = =IF(AI$67<"",INDEX(INDIRECT("'"&AI$67&"'!$A$1:$IV $50000"),TIME,MATCH($C71,INDIRECT("'"&AI$67&"'!$A$ 2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&"'!$A$1:$IV $50000"),TIME,MATCH($C71+1,INDIRECT("'"&AI$67&"'!$ A$2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&"'!$A$1:$ IV$50000"),TIME,MATCH($C71+2,INDIRECT("'"&AI$67&"' !$A$2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&"'!$A$1 :$IV$50000"),TIME,MATCH($C71+3,INDIRECT("'"&AI$67& "'!$A$2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&"'!$A $1:$IV$50000"),TIME,MATCH($C71+4,INDIRECT("'"&AI$6 7&"'!$A$2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&"'! $A$1:$IV$50000"),TIME,MATCH($C71+5,INDIRECT("'"&AI $67&"'!$A$2:$IV$2"),0))+INDEX(INDIRECT("'"&AI$67&" '!$A$1:$IV$50000"),TIME,MATCH($C71+6,INDIRECT("'"& AI$67&"'!$A$2:$IV$2"),0)),"") with Time equal to the following formula: =MATCH(1,(INDIRECT("'"&AI67&"'!C1:C50000")=G67)*(I NDIRECT("'"&AI67&"'!E1:E50000")=G1),0) |
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com