Indirect formula

Can someone please explain the formula below for me?....
INDIRECT ("'"&BE$6&"'!"&"$G:$G)

return the values in column G from the sheet which is named in cell BE6. I assume that it is part of a larger formula...

It may be very slow, as it uses a whole column reference...

You sent me a PM saying that was the complete formula (please keep conversations in your thread...).

In that case, I have no idea what it is for. If entered as an orinary formula, it will retun zero. If enered as an array formula, it returns the value in G1 of the sheet referred to. What were you hoping that it would do?

I misread your pm...

Here is your formula, broken down into bite-sized chunks…

=IF(ISERROR(
SUMIFS(INDIRECT("'"&BE$6&"'!"&"$G:$G"),INDIRECT("'"&BE$6&"'!"&"$R:$R"),$A39,INDIRECT("'"&BE$6&"'!"&"$F:$F"),"<>52711",INDIRECT("'"&BE$6&"'!"&"$C:$C"),10)),
Pick up the sheetname from BE6 and sum the values in column G where…
The value in R is equal to the value in a39, and
The value in F is anything other than 52711 and
The value in C is 10

If all of that returns an error
0, return zero; otherwise
SUMIFS(INDIRECT("'"&BE$6&"'!"&"$G:$G"),INDIRECT("'"&BE$6&"'!"&"$R:$R"),$A39,INDIRECT("'"&BE$6&"'!"&"$F:$F"),"<>52711",INDIRECT("'"&BE$6&"'!"&"$C:$C"),10)) return the calculated value.

PLUS … a similar formula
+IF(ISERROR(
SUMIFS(INDIRECT("'"&BE$4&"'!"&"$A$30:$EO$30"),INDIRECT("'"&BE$4&"'!"&"$A$4:$EO$4"),BE$5,INDIRECT("'"&BE$4&"'!"&"$A$5:$EO$5"),$A39)),
0,
SUMIFS(INDIRECT("'"&BE$4&"'!"&"$A$30:$EO$30"),INDIRECT("'"&BE$4&"'!"&"$A$4:$EO$4"),BE$5,INDIRECT("'"&BE$4&"'!"&"$A$5:$EO$5"),$A39))

PLUS another similar formula.
+IF(ISERROR(
SUMIF(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39,(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"I:I")))),
0,
SUMIF(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39,(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"I:I"))))

It’s a mess. Whole column references will be slow, unless you do have more than 1,000,000 rows. Also the clumsy use of IF(ISERROR… an Excel 2003 and before thing will slow it up further.

I’d use:

=IFERROR(SUMIFS(INDIRECT("'"&BE$6&"'!"&"$G:$G"),INDIRECT("'"&BE$6&"'!"&"$R:$R"),$A39,INDIRECT("'"&BE$6&"'!"&"$F:$F"),"<>52711",INDIRECT("'"&BE$6&"'!"&"$C:$C"),10),0)+IFERROR(SUMIFS(INDIRECT("'"&BE$4&"'!"&"$A$30:$EO$30"),INDIRECT("'"&BE$4&"'!"&"$A$4:$EO$4"),BE$5,INDIRECT("'"&BE$4&"'!"&"$A$5:$EO$5"),$A39),0)+IFERROR(SUMIF(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39,(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"I:I"))),0)

AND I would change all the whole column references to something sensible.

Thanks so much Glenn. I'm using your formula and it's working great. The only problem is I'm trying to exclude 2 rows from the sheet BE$4 and also exclude 52712 in addition to 52711 in she BE$6. How can I factor these changes into your suggested formula? And finally, can you please explain INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39 for me, as it's not quite the same as the ones you explained earlier. Thanks

This is now nigh on impossible without seeing the sheet and also knowing what rows you want to exclude. Can you post your sheet, or a suitably anonmyised sample?

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.