I'm trying to use the indirect function to link to branch tabs throughout my file.
Count column = +COUNTIF(INDIRECT("'"&$A8&"'!$C$6:$C$400"),$C$4)/5. This works for each branch (35, 12, 9, and so on).
Hours column = +SUMPRODUCT(--(Houston!$C$6:$C$400=$C$4),--(Houston!$F$6:$F$400=D$5),Houston!$BG$6:$BG$400). That works fine for Houston (I have that copied down for the Hours column for now; that's why the number is the same for each branch).
I want to change the hours column formula so that I can copy it down without changing the branch name every time so I used:
+SUMPRODUCT(--(INDIRECT("'"&$A8&"'!$C$6:$C$400)=$C$4),--(Houston!$F$6:$F$400=D$5),Houston!$BG$6:$BG$400) just on the first leg of the formula and I get the pop up box with "There's a problem with this formula..."
Can SUMPRODUCT and INDIRECT work together? If so, what is the fix? Thanks in advance!