vlookup across multiple pages
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. |
vlookup across multiple pages
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. |
vlookup across multiple pages
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. . |
All times are GMT +1. The time now is 10:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com