Adding cells from different Excel files

Question

I have data from multiple excel files I need to add together. All the excel files have one worksheet in them and the format of the worksheet are all the same. I want my master excel file to have an aggregate of all the data in the other excel files. 

For example, in my master excel file for cell C19 I want the sum of all the other C19's from the other excel files. The other excel files will be updated periodically and I would like the master excel file to update automatically without the need for me to open the other excel files. 

I am using Excel 365 for business and all the excel files are on SharePoint. 

Thank you.

Answer 1

Hi Yasith L Wickramarachchi,

It is not feasible to use an external reference to another workbook if the workbook is located in OneDrive/SharePoint. Based on your description, I suggest that you can put the contents of two Workbooks into different sheets inside a Workbook. To address your concern about this situation, We’d suggest you kindly vote this UserVoice:  Links between spreadsheets in Excel Online. The high votes may make the team pay more attention to this. This is the best platform to let us hear from you and to make our products and services better for you and others.

Thank you for your understanding!

Regards,

Sukie

Answer 2

Here are a couple of articles that show how to do what you want

Depending on how the data / worksheets are setup, you could even use "Get and Transform" to import the values into a single workbook then create a Pivot table to generate the required totals. After the initial setup, updating data is quite quick and easy. These "new" features are definitly worth the effort to learn. Once you figure out how to use them they are a real time saver

**************************

************  Free Webinars ****************

Even if you can't participate at the scheduled times, do sign up. You can get the example file downloads AND download the recording of the session to play at a time convenient to you .  They are all well worth the time.

PS: I get NOTHING! for these recommendations

************************************

Wed Aug 8 - Tues Aug 14
https://www.myonlinetraininghub.com/excel-webinars 

teach you how to build this interactive Excel Dashboard that you can update in less than 1 minute.

we'll send you a link to the webinar replay. You can download a copy of the video recording and the Excel workbook used in the webinar.

Dashboards Using Power Query & Power Pivot   Wed Aug 8 - Tues Aug 14
https://www.myonlinetraininghub.com/excel-webinars 

use Power Query to get data from multiple sources, filter and clean it, send it to Power Pivot and create relationships between data tables.
Then I'll mash it up in PivotTables to create this interactive Excel Dashboard

Power BI Dashboards for Excel Users  Watch replay
https://www.myonlinetraininghub.com/excel-webinars

Power BI can get data from 60 different sources
It uses the the same Power Query and Power Pivot tools available in Excel
Build dashboards with Drag & Drop navigation
Interactive visualisations allow users to cross highlight and filter by clicking on the charts
Allow users to drill down to lower levels and even underlying data (with your permission)

All three webinars have downloadable workbooks

***********************************

The 7 Steps to Getting Started with Macros & VBA - 
(& actually understanding what the code means!)

With Jon Acampora, Microsoft MVP
https://www.excelcampus.com/free-macros-webinar

**********************************

 MyExcelOnline Free webinars by John Michaloudis and Oz Du Soleil
https://www.myexcelonline.com/free-excel-webinars   
(closed now, but you can register for email when the webinars are offered again.

Free Power Query & Data Cleansing Webinar   118min   
  *  The most powerful feature in Excel since VLOOKUP...it's called POWER QUERY (GET & TRANSFORM) & it will save you HOURS each day!!! 
  *  Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)... AND without using VBA or Macros! 
  *  Data Cleansing Excel Tips & Special Bonus attendee material that you can keep and use to become better at Excel straight away, skyrocketing your personal & professional development! 
  *  Plus learn how to use Data Models, Unpivot, Parse Text, Consolidate Reports & Flash Fill!
  *  Data Models- Analyzing multiple datasets as if they are a single dataset
  *  Parsing Text- Splitting data apart, ie when name aage and email are all in same cell
  *  Inner Join – Comparing lists to extract data that appears on both lists
  *  Unpivot – convert a report into raw data so that I is useful for analysis
  *  Consolidate multiple worksheets 

Free Pivot Table Webinar  
https://www.myexcelonline.com/free-excel-webinars   

During This Free Excel Webinar You Will Learn …
  *  How to set up your data and create a Pivot Table in less than 3 minutes which will increase your efficiency!
  *  How to put your key business metrics like Year to Date Sales, Monthly Variations and Top 10 Customers in an interactive Pivot Table, taking your analytical skills to another level!
  *  How to create an Interactive Dashboard with Slicers & Pivot Charts that will WOW your boss and get you noticed by top Management!
  *  Pivot Table tips & special Bonus attendee material that you can keep and use to become better at Excel straight away which will skyrocket your personal development!

As well, go to the website, https://www.myexcelonline.com/blog/contact/  , and search for “The Ultimate Excel Resource Guide”. It is a free 80+ page PDF download of links to Excel blogs, forums, courses, books and Add-ins.  Great place to start looking for help

Free Excel Formulas Training   
https://www.myexcelonline.com/free-excel-webinars   

The most popular Excel Formulas that get asked during interviews & ones that you NEED to know!:
   *  A proven step by step guide to creating Excel Formulas in SECONDS!
   *  The limitations of using a VLOOKUP and why INDEX&MATCH is better! 
   *  3 TIPS that the Excel experts & MVPS use that will enhance your Formula knowledge!
You will also be able to download the Excel workbook used in the training so you can follow along & practice!

Macros & Vba Training   
https://www.myexcelonline.com/free-excel-webinars   

Learn How To Record MACROS & Write VBA Code within 1 HOUR!
AUTOMATE repetitive & boring tasks with a single press of a button that will SAVE YOU Time & Money! 
   *  How to record a Macro and create interactive buttons to run it! 
   *  How to automate your repetitive Excel tasks using the VBA Editor! 
   *  VBA code by way of simple explanations of the VBA coding language that even my 5 year old understands! 
   *  Saving and sharing your Macros! 
   *  You will also be able to download the Excel files I use in the training that contain code examples.

***************************************

5 Secrets to Understanding Pivot Tables  
https://www.excelcampus.com/pivot-secrets-webinar 

learn how to use Pivot Tables to create interactive reports and save tons of time with your job. 
1. If you have ever had any of the following questions about pivot tables in Excel, then this free training is for you!
     What exactly is a pivot table?
     How do pivot tables work?  
     What does each area of the pivot table do? 
     What is the correct layout and format for the source data?
     Why should I learn pivot tables and how will they benefit me?
2. How we can use pivot tables to quickly summarize & analyze data without complex formulas or macros.
3. What each pivot table area does, and how to eliminate trial & error with building pivot tables.
4. A sneak peek of Power Query and how to unpivot our data with this powerful new data tool.
5. The most CRITICAL step to using pivot tables.  Hint: it's all about the source data.

Introduction to Pivot Tables and Dashboards (web videos, no signup required)
https://www.excelcampus.com/charts/pivot-tables-dashboards-part-1/ 

Jon Acampora	February 3, 2015
Bottom line: In this video series you will learn how to use Pivot Tables to create meaningful reports and interactive charts and dashboards.

Video 1- Introduction to Pivot Tables
In this first video we are going to learn how to setup your source data, then create a Pivot Table and Pivot Chart. I also show you how to reformat the chart to upgrade the look from the default settings.
4 page: Pivot Table Source Data Checklist - Excel Campus.pdf (262.2 KB)
 Intro To Pivot Tables Part 1.xlsx

In part 2 of the series we learn use the different calculation types to quickly answer questions about your data.  I also explain how to update your pivot table with more data.
https://www.excelcampus.com/charts/pivot-tables-dashboards-part-2/ 
 Intro To Pivot Tables Part 2.xlsx
Part 2 additional data  Sales Data For January 2015.xlsx

In part 3 of the series we create the dashboard and add slicers to make it interactive.  Learn how to quickly create an interactive dashboard using pivot tables and pivot charts.
 Intro To Pivot Tables And Dashboards - Part 3.xlsx

**************************

Excel 2016 Linking Worksheets 
https://www.computer-pdf.com/office/excel/583-tutorial-excel-2016-linking-worksheets.html 
Download free tutorial Microsoft Office Excel 2016: Linking Worksheets, PDF book by Pandora Rose Cowart University Of Florida.
In this workshop we will insert, delete and rename worksheets; change data and formatting on multiple sheets at the same time; link worksheets to create a Totals page; move sheets into different workbook (file); and change the view to see multiple books and sheets at the same time.

Worksheets in VBA Coding and in Worksheet Formulas.  
http://dmcritchie.mvps.org/excel/sheets.htm 
Examples of cell references to another sheet or another workbook.  HYPERLINK worksheet formulas. 

create external reference in Excel to refer to another sheet or workbook
https://www.ablebits.com/office-addins-blog/2015/12/08/excel-reference-another-sheet-workbook/ 
This short tutorial explains the basics of an external reference in Excel, and shows how to reference another sheet and workbook in your formulas. Continue reading →
by Svetlana Cheusheva 2015 12 08

Retrieve Values from Many Workbooks 
http://www.excel-university.com/retrieve-values-from-many-workbooks/ 
April 6, 2017 Jeff Lenning
 
I have a folder with several hundred workbooks, and each workbook may contain any number of worksheets. For example, some workbooks have two sheets, some have three sheets, and some have […] Read more

Answer 3

Hi,

I am an Independent Advisor here to help you.
This is possible, for a limited number of files you will need no VBA script:

='SUM(https://XXX.sharepoint.com//Documents/[FILE.xlsm]Sheet1'!$C$19,https://XXX.sharepoint.com//Documents/[FILE2.xlsm]Sheet1'!$C$19)

Please let me know I'm happy to try to solve your problem.