J
jbo
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),(INDIRECT("'"&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$67&"'!$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)*(INDIRECT("'"&AI67&"'!E1:E50000")=G1),0)
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),(INDIRECT("'"&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$67&"'!$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)*(INDIRECT("'"&AI67&"'!E1:E50000")=G1),0)