Vlookup across multiple sheets

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.

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


--
Regards
Frank Kabel
Frankfurt, Germany

Brian wrote:
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.

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

"Frank Kabel" wrote in message
...
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")))


--
Regards
Frank Kabel
Frankfurt, Germany

Brian wrote:
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.

Aladin Akyurek wrote:
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

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.

"Frank Kabel" wrote in message
...
Aladin Akyurek wrote:
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

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

--

Regards,

Peo Sjoblom

"Frank Kabel" wrote in message
...
Aladin Akyurek wrote:
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

Hi Aladin
thanks for the addition (forgot about this)

--
Regards
Frank Kabel
Frankfurt, Germany

Aladin Akyurek wrote:
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.

"Frank Kabel" wrote in message
...
Aladin Akyurek wrote:
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