Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.