Using the contents of a cell to refer to a worksheet name in a formula

Question

I have the names of dealers along with the associated data located in various tabs in a particular workbook. I wrote a sumif formula for one and included the names of the dealers in the cell to the left... I want to change my formula so that when I drag it down it takes the names of the dealers (from the row of cells to the left of the formula) and use it to populate the workbook references in the sumif formula.

=SUMIF(Benco!C:C,Sheet2!G1,Benco!L:L)

Is what I start with, I change the references to:

=SUMIF('F3'!C:C,Sheet2!G1,'F3'!L:L)

but then I'm prompted for the location of the workbook containing worksheet F3... I also tried enclosing "F3" in quotes to no avail.

Is it possible to make a formula like this work?

Answer 1

The INDIRECT worksheet function can handle this. For your case, the syntax would be

=SUMIF(INDIRECT(F3 & "!C:C"),Sheet2!G1,INDIRECT(F3 & "!L:L"))

You can use indirect to build most any range reference on the fly by just putting the range syntax in string format. You will get a bad reference error (#REF) if your reference does not exist.