Solved

Excel - Add up values from a column from multiple workbooks

Posted on 2011-09-15
9
224 Views
Last Modified: 2012-06-27
Hi Excel Experts,

I have multiple Excel Workbooks.  (Example attached)   Jaggar---Bookstore.zip

Inside of these files you will see a sheet which looks like this:  Sheet 1
Each file has different numbers (in different order).  Here is another workbook screenshot: Sheet 2
What I need to do is add up all the numbers from the sheet into a single data set.  (Only the yellow highlighted part).  [Don't pay attention to the other sheets, in the workbook.  We are only focusing on the yellow highlighted part).  (I know I could use a pivot table, and copy all of the data into a single sheet; but I would prefer to do this using a formula).

Can someone help?
0
Comment
Question by:activematx
  • 4
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36546665
Hello,

without opening the attached files: You can use Vlookup on closed files to pull values.

In a workbook with PLU numbers in column A, use something like

=vlookup(A2,[FirstBook.xls]Sheet1!$E$1:$F$1000,2,False)+vlookup(A2,[SecondBook.xls]Sheet1!$E$1:$F$1000,2,False)+vlookup(A2,[ThirdBook.xls]Sheet1!$E$1:$F$1000,2,False)

The easiest way to create the vlookups is to have all files open. Start typing the Vlookup formula and click into the external workbook to select the lookup range.

When you've entered the formula, you can close the external workbooks and Excel will adjust the file path.

cheers, teylyn
0
 
LVL 9

Author Comment

by:activematx
ID: 36546706
Hi Teylyn,

I tried doing it.  I put into a new workbook this formula:

=VLOOKUP(A1,'[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$3:$F$153,2,FALSE)+VLOOKUP(A1,'[Zone 2 - Jag Bookstore.xlsx]Variance'!$E$3:$F$74,2,FALSE)+VLOOKUP(A1,'[Zone 3 - Jag Bookstore.xlsx]Variance'!$E$3:$F$68,2,FALSE)+VLOOKUP(A1,'[Zone 4 - Jag Bookstore.xlsx]Variance'!$E$3:$F$125,2,FALSE)+VLOOKUP(A1,'[Zone 5 - Jag Bookstore.xlsx]Variance'!$E$3:$F$127,2,FALSE)

Open in new window


However, I get a #N/A when I do that formula.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36546741
If vlookup returns N/A that means the search term is not found. Can you confirm that the value in A1 is actually present in the respective ranges of the workbooks?

Look out for leading/trailing blanks, numbers stored as text, and the like.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36546748
Then, of course, if a PLU is not present in ALL five workbooks, you need to trap errors, for example like this

=IFERROR(VLOOKUP(A2,'[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$1:$F$153,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 2 - Jag Bookstore.xlsx]Variance'!$E$2:$F$74,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 3 - Jag Bookstore.xlsx]Variance'!$E$2:$F$68,2,FALSE),0)
+IFERROR(VLOOKUP(A2,'[Zone 4 - Jag Bookstore.xlsx]Variance'!$E$2:$F$125,2,FALSE),0)

etc.

cheers, teylyn
0
 
LVL 9

Author Comment

by:activematx
ID: 36546769
Looks like it is working.  Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0
 
LVL 9

Author Comment

by:activematx
ID: 36546774
This is what I am using:

=VLOOKUP(A3,'HNHADocs:YEAR END AUDIT:FY11 Closeout/Audit:Inventory Related:HAVO INVENTORY:Jaggar - Bookstore:[Zone 1 - Jag Bookstore.xlsx]Variance'!$E$3:$F$153,2,FALSE)
0
 
LVL 9

Author Comment

by:activematx
ID: 36546802
Is there a way, to make it say "0" instead of #N/A for the ones that don't work.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36546825
Did you see my last comment? That will suppress N/A and put in 0 instead.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36546838
activematx,

No points for this, please, as teylyn has answered your question.

For more information about VLOOKUP, including how to return an alternate answer instead of #N/A, please see:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

The article has voting buttons at the top; if you like it, then please vote yes :)

Patrick
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question