Vlookup across multiple sheets

[Brian]

I am trying to build a formula that will do a vlookup
across multiple sheets and give me the sum of the results
in a summary page.

Here is what I have tried.

=SUM(VLOOKUP(B7,'1:31'!B736,3,FALSE))

I have tried other variants of the this including putting
the sheets ('1:31')in front of the vlookup, and can not
get it to work.

I would appreciate any help I can get.

B.

 

[Frank Kabel]

Hi Brian
if your sheets are really named 1 to 31 try the following formula to
sum all values in column D for which column B equals B7

=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW(1:31) & "'!B7:B36"),
B7,INDIRECT("'" & ROW(1:31) & "'!D736")))

 

[Aladin Akyurek]

ROW(1:31) yields a non-robust formula.

Hi Brian
if your sheets are really named 1 to 31 try the following formula to
sum all values in column D for which column B equals B7

=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW(1:31) & "'!B7:B36"),
B7,INDIRECT("'" & ROW(1:31) & "'!D736")))

 

[Frank Kabel]

ROW(1:31) yields a non-robust formula.

Appreciate your comment - could you explain why this is non-robust and
what alternative you would use
Did you mean to replace
ROW(1:31)
with
ROW($1:$31)

Regards
Frank

 

[Aladin Akyurek]

Inserting one or more rows before the formula row would cause havoc. Users
like to beautify their spreadsheets and will fail to notice/see the
incorrect results due to a formula, broken by their beuatifying actions.

The idiom to use is:

ROW(INDIRECT("1:31"))

even better:

ROW(INDIRECT("1:"&ExpressionThatComputesN))

even if all this is going to cost more.

 

[Peo Sjoblom]

Try

ROW(INDIRECT("1:31"))

that way the sheet name won't change if for instance a row is inserted above
the row where the formula is.
Any row(1:31) or row($1:$31) will change to row(2:32) while the one with
indirect will stay hard coded

 

[Frank Kabel]

Hi Aladin
thanks for the addition (forgot about this)