Combining VLOOKUPS?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PT40 - 31 Jan 2008 22:44 GMT

Hello,

I am trying to tally the fields in several sheets of the same workbook. I
can use either hidden fields or link one cell to a hidden sheet with several
lookups, but would really like to put it all in one cell. I've put an example
below of what I would like to do. Is there an easy way to go about this? I'd
like to tally the cells of 7 sheets manytimes over.

Thank you

=(VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-3'!$C$4:$J$25, 5,
FALSE)+(VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4,
'2-5'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-6'!$C$4:$J$25, 5,
FALSE)+(VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4,
'2-8'!$C$4:$J$25, 5, FALSE))

Reply to this Message

Harlan Grove - 31 Jan 2008 23:25 GMT

PT40 <P...@discussions.microsoft.com> wrote...

>I am trying to tally the fields in several sheets of the same
>workbook. I can use either hidden fields or link one cell to a
>hidden sheet with several lookups, but would really like to put it
>all in one cell. I've put an example below of what I would like to
>do. Is there an easy way to go about this? I'd like to tally the
>cells of 7 sheets manytimes over.

...

You have several more left parentheses than right parentheses, so I've
removed the unnecessary ones.

>=VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE)
>+VLOOKUP(C4, '2-3'!$C$4:$J$25, 5, FALSE)
[quoted text clipped - 3 lines]
>+VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE)
>+VLOOKUP(C4, '2-8'!$C$4:$J$25, 5, FALSE)

Your ranges aren't too big, and if there were only one instance of the
value of C4 in the first columns of each range on the different
worksheets, you could use

=SUM(SUMIF(INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7","2-8"}
&"'!C4:C25"),C4,INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7",
"2-8"}&"'!G4:G25")))

Better, enter the worksheet names in a range, name the range W, and
shorten this formula to

=SUM(SUMIF(INDIRECT("'"&W&"'!C4:C25"),C4,INDIRECT("'"&W&"'!G4:G25")))

If your ranges were big or you could have multiple instances of the C4
value in some of these ranges, summing separate VLOOKUP calls is
optimal.

Reply to this Message

PT40 - 31 Jan 2008 23:45 GMT

Removing the parenthesis worked. Thanks much

> PT40 <P...@discussions.microsoft.com> wrote...
> >I am trying to tally the fields in several sheets of the same
[quoted text clipped - 32 lines]
> value in some of these ranges, summing separate VLOOKUP calls is
> optimal.

Reply to this Message