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


.