Vlookup across multiple sheets

  • Thread starter Thread starter Brian
  • Start date Start date
B

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'!B7:D36,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.
 
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) & "'!D7:D36")))
 
ROW(1:31) yields a non-robust formula.

Frank Kabel said:
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) & "'!D7:D36")))
Click to expand...
 
Aladin said:
ROW(1:31) yields a non-robust formula.
Click to expand...

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
 
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.
 
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
 
Back
Top