MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

vlookup across multiple pages

• Follow

```I'm trying to use vlookup to give me a summary of some number throughout a
workbook.  It has to sum across about 25 worksheets.  not all worksheets have
the date I'm looking up so I will need to set the range_lookup to true so
that it will add the last inventory count before my specified date.  I can
use the following on a small amount of sheets...but when I try it on the
large number of worksheets, it ultimately gets messed up.

VLOOKUP(B6,ARCHIES_CORNER!\$A\$12:\$D\$100,2,TRUE) +
VLOOKUP(B6,BARN_YARD!\$A\$12:\$D\$100,2,TRUE) +
VLOOKUP(B6,BEAVER_DAM!\$A\$12:\$D\$100,2,TRUE) + VLOOKUP....etc

Any suggestions?  SUMIF doesn't seem to work because of the date issue.
```
 0

```Group your data sheets, and select a free cell. Enter a formula like
(reference cell B6 from your summary sheet)

=VLOOKUP(Summary!B6,\$A\$12:\$D\$100,2,TRUE)

That will place that formula on every sheet.

And then use a formula like this for the sum:

--

HTH,
Bernie
MS Excel MVP

"Mazkot" <Mazkot@discussions.microsoft.com> wrote in message
news:5F4F1453-553D-4E95-B751-9A1B3FBDE76D@microsoft.com...
> I'm trying to use vlookup to give me a summary of some number throughout a
> workbook.  It has to sum across about 25 worksheets.  not all worksheets
> have
> the date I'm looking up so I will need to set the range_lookup to true so
> that it will add the last inventory count before my specified date.  I can
> use the following on a small amount of sheets...but when I try it on the
> large number of worksheets, it ultimately gets messed up.
>
> VLOOKUP(B6,ARCHIES_CORNER!\$A\$12:\$D\$100,2,TRUE) +
> VLOOKUP(B6,BARN_YARD!\$A\$12:\$D\$100,2,TRUE) +
> VLOOKUP(B6,BEAVER_DAM!\$A\$12:\$D\$100,2,TRUE) + VLOOKUP....etc
>
> Any suggestions?  SUMIF doesn't seem to work because of the date issue.

```
 0

```Thanks...it never crossed my mind to do the vlookup on each page then just
sum them up....

"Bernie Deitrick" wrote:

> Group your data sheets, and select a free cell. Enter a formula like
> (reference cell B6 from your summary sheet)
>
> =VLOOKUP(Summary!B6,\$A\$12:\$D\$100,2,TRUE)
>
> That will place that formula on every sheet.
>
> And then use a formula like this for the sum:
>
>
> --
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Mazkot" <Mazkot@discussions.microsoft.com> wrote in message
> news:5F4F1453-553D-4E95-B751-9A1B3FBDE76D@microsoft.com...
> > I'm trying to use vlookup to give me a summary of some number throughout a
> > workbook.  It has to sum across about 25 worksheets.  not all worksheets
> > have
> > the date I'm looking up so I will need to set the range_lookup to true so
> > that it will add the last inventory count before my specified date.  I can
> > use the following on a small amount of sheets...but when I try it on the
> > large number of worksheets, it ultimately gets messed up.
> >
> > VLOOKUP(B6,ARCHIES_CORNER!\$A\$12:\$D\$100,2,TRUE) +
> > VLOOKUP(B6,BARN_YARD!\$A\$12:\$D\$100,2,TRUE) +
> > VLOOKUP(B6,BEAVER_DAM!\$A\$12:\$D\$100,2,TRUE) + VLOOKUP....etc
> >
> > Any suggestions?  SUMIF doesn't seem to work because of the date issue.
>
>
> .
>
```
 0

2 Replies
395 Views

Similiar Articles:

7/28/2012 8:12:20 AM