Excel 2007 : vlookup across multiple worksheets

**mazkot** · 05-03-2010, 01:15 PM

I would like to summarize information from multiple worksheets. Attached is a sample worksheet. I would like to use vlookup to lookup inventory quantities based on the value in column 1(months). Is this possible?

Thanks

**JBeaucaire** · 05-03-2010, 02:04 PM

I believe a multi-page pivot table is the tool for this. Here's a primer from Microsoft on creating that:

Consolidate multiple worksheets into one PivotTable report

And one from one of old-faithful code sites, Contextures:

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges

**mazkot** · 05-07-2010, 10:14 AM

What I'm trying to do is to get a summary of the stock at any given time...in this case on the first of each month. How do I get a pivot table to do that? I've tried to use something like =VLOOKUP(A2,Store1!A12:A200,2,TRUE)+VLOOKUP(A2,Store2!A12:A200,2,TRUE)VLOOKUP(A2,Store3!A12:A200,2,TRUE)

But that's not working...

**mazkot** · 05-07-2010, 11:30 AM

Okay i got it to work in this little test ...but I have an actual workbook that has probably 20 worksheets and when I try to do the vlookup addition it ALWAYS gets messed up. I even had trouble getting vlookup to show the right answer in this small workbook. I attached a copy that is doing what I want it to do. Does anyone know a shortcut to making this work in a larger workbook?

**am0251** · 05-07-2010, 02:24 PM

Try this one , if you want to use Vlookup , all the coloum (in this file is "A") must have all same value , if (B,C,D) is empty put "0" , wish it can help

**mazkot** · 05-07-2010, 02:49 PM

thanks but that doesn't do what I was looking for. With vlookup it will look for the date from column one and if cannot find an inventory count for that date, it will fill in the last count from a previous date.