Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Combining data from multiple workbooks

0

Hi guys!

Im trying to combine data from multiple separate workbooks into a single cell in a third workbook.

I use the & to combine data from the same workbook but how can I use that to combine data from multiple separate workbooks?

Here is a sample of what I have to combine data from the same file...

=A1&" "&Sheet2!A1&" "&Sheet3!A1

Thanks for any help!

Ginger

Answer
Discuss

Answers

0

If you have sheets labeled 1 through 10 (Sheet1, Sheet2...Sheet10) and you insert a worksheet at the end, I call it Consol ~ for consolidated.  Use the formula:  =Sum(Sheet1:Sheet10!A1 in Cell A1 on the Consol tab.  You can then copy that formula to the cells where you want the sheets totaled for that cell.

Note: it will give an error if the formula for totaling is not the last sheet (in other words, you can NOT have Sheet1, Sheet2, Consol, Sheet3.  Consol must be either the last of the first Sheet !

Discuss

Discussion

Thanks for your help. Unfortunately I need to access the data from multiple separate workbooks instead of just the same workbook and it might not always be from the same cell in each worksheet.

But thanks for this great tip to get data more easily form multiple worksheets in the same workbook!
gingertree (rep: 2) Apr 7, '17 at 4:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login