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,Adams!$A$12:$D$100,2,TRUE)+  
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.

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:

=SUM(Adams:Last_Sheet!E9)

-- 

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,Adams!$A$12:$D$100,2,TRUE)+
> 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. 

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:
> 
> =SUM(Adams:Last_Sheet!E9)
> 
> -- 
> 
> 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,Adams!$A$12:$D$100,2,TRUE)+
> > 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. 
> 
> 
> .
>