Linking Multiple Workbooks - Sharepoint

Question

Hi there,

I currently link multiple workbooks together, pulling the data from one to another.  The spreadsheets are saved on sharepoint and when I update one, the other doesn't always update.  I have read that you need to have both spreadsheets open in order for them both to update accurately.

Is there a better, less temperamental way of pulling data from one workbook to another?

Thanks

Answer 1

I use PowerQuery (Get & Transform) to pull data from other workbooks. The basic steps are:

1. Your source data is best in a named range or Excel table, not just a grid of data.
2. Using PowerQuery, get files from SharePoint
3. Filter the result to the file you need
4. CLick the Binary link next to the file name. This will just get the data from THAT file as opposed to the more powerful "combine binaries" which is designed to combine multiple files.
5. Select the range or table you want to pull in
6. further filter the data and/or delete columns to only have what you ultimately want in your destination Excel file.
7. Load that query to an Excel range.

Now when you REFRESH ALL, the data will update.

Depending on the size of your sharepoint site, it can be a bit slow, but it has two primary benefits:

1. The files can be closed when updated.
2. You can pull in an entire range (table) of data, not just a single cell. Your destination cells will grow/shrink as needed depending on what happened in the source workbook.

Answer 2

Hi, what kind of formulas do you have, only sumproduct will pull information from closed workbooks

Answer 3

Hi Heather,

Thanks for your information.

To better understand your situation and reproduce on our side, could you provide detailed steps about how you link the workbook together in Excel? Also, please capture related screenshots.

Regards,

Tina

Answer 4

Thanks for your response Tina.  

We're using Excel 2016 and using the formula ='<Sharepoint URL>[<Spreadsheet Name>]<Sheet Name>'!<Cell Reference> to pull data from another spreadsheet.  We have a Final Data spreadsheet which pulls data from a Calculations Spreadsheet which in turn pulls data from a Raw Data spreadsheet.

The workbooks were created offline but have since been uploaded to our Sharepoint site.

Any help you can provide would be greatly appreciated.

Thanks,

Heather

Answer 5

Hi Heather,

Could you clarify how you link the multiple workbooks together? Use a specific formula or the Data Connection feature in Excel?

Besides, how did you create the workbooks? Create in local then upload them to SharePoint or create them in SharePoint directly?

Do the workbooks always update if you put the workbooks in local computer?

Please provide Excel version as well.(Excel>File>Account)

Regards,

Tina