Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Vlookup across multiple sheets

0 views
Skip to first unread message

Brian

unread,
Mar 10, 2004, 4:07:47 PM3/10/04
to
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.

Frank Kabel

unread,
Mar 10, 2004, 4:21:12 PM3/10/04
to
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")))


--
Regards
Frank Kabel
Frankfurt, Germany

Aladin Akyurek

unread,
Mar 10, 2004, 5:26:10 PM3/10/04
to
ROW(1:31) yields a non-robust formula.

"Frank Kabel" <frank...@freenet.de> wrote in message
news:eCzMbWuB...@TK2MSFTNGP10.phx.gbl...

Frank Kabel

unread,
Mar 10, 2004, 5:40:21 PM3/10/04
to
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

Aladin Akyurek

unread,
Mar 10, 2004, 5:50:47 PM3/10/04
to
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" <frank...@freenet.de> wrote in message

news:Oc$DqCvBE...@TK2MSFTNGP11.phx.gbl...

Peo Sjoblom

unread,
Mar 10, 2004, 5:58:17 PM3/10/04
to
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" <frank...@freenet.de> wrote in message

news:Oc$DqCvBE...@TK2MSFTNGP11.phx.gbl...

Frank Kabel

unread,
Mar 10, 2004, 6:05:13 PM3/10/04
to
Hi Aladin
thanks for the addition (forgot about this)

--
Regards
Frank Kabel
Frankfurt, Germany

0 new messages