Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Excel - Add up values from a column from multiple workbooks

Posted on 2011-09-15
Medium Priority
243 Views
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:
Each file has different numbers (in different order).  Here is another workbook screenshot:
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
Question by:activematx
• 4
• 4

LVL 50

Expert Comment

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

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)
``````

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

LVL 50

Expert Comment

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

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

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

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

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

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

LVL 93

Expert Comment

ID: 36546838
activematx,

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month10 days, 11 hours left to enroll