Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


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


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
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

Your Answer


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.