Excel & Sharepoint links

**ChocksterNo1** · 06-04-2013, 05:25 AM

Hi

Think this may have more to do with Sharepoint than excel, but not sure where this post should sit?

I have a master excel sheet (on sharepint) that is linked to around 30 excel files in different folders on sharepoint. When I update the data on the master spreadsheet, it updates all the other excel workbooks when they are opened and the update links button is chosen.

I want a summary excel spreadsheet (in sharepoint) linked to the individual worksheets that will update when the file is opened, however when I open the file and update links, the figures are replaced with #REF!. When the individual file is opened up, the figures reappear, but it's impractical to open up every excel worksheet, just to update this workbook.

Has anyone come across this before?

Any help would be greatly appreciated.

Thanks

Chock

**:) Sixthsense :)** · 06-04-2013, 06:31 AM

What is the formula you used in your master file to refer it to other individual files?

**ChocksterNo1** · 06-04-2013, 06:50 AM

Thanks

Ok, we were advised that data validation lists couldn't work across different workbooks, so we have 3 worksheets in the master spreadsheet with data.

In each individual worksheet, we have the same 3 spreadsheets that link to the master spreadsheet. Starts at cell B4

Each cell is looking at the same cell on the master spreadsheet.

='http://portal/collaboration/finance/Systems/PILOT/RESTRICTED - FINANCE USE ONLY/[FINANCE USE ONLY - Master Secondary Systems Spreadsheet.xlsm]BUDGET'!B4

The individual spreadsheet's have formulas that look at the individual sheet within the workbook.

=SUMIF(BUDGET!$C$4:$C$1000,$E6,BUDGET!$D$4:$D$1000)

When we open the individual worksheet, I can click on update links and it updates all the info on the worksheet.

So when we try to produce a summary spreadsheet that look sat specific cells on the individual spreadsheets, it doesn't update the links unless we have the source files open??

Thanks

Chock

**:) Sixthsense :)** · 06-04-2013, 06:59 AM

Sumif won't work with closed workbooks

Convert it to Sumproduct.........

**ChocksterNo1** · 06-04-2013, 07:15 AM

the sumif is only looking at data on an open worbook.

The summary sheet would just be looking at the total row in the individual worksheet, so on the summary workbook the formula would be

=+'http://portal/collaboration/finance/Systems/PILOT/FM Soft Services/Financial Forecasting Tool - P12520.xlsm'!BudgetTable[[#Totals],[FULL YEAR BUDGET]]

The individual workbooks have tables, would this cause a problem? or is as you say, the fact that I have SUMIF's in the individual worksheets in some tables that it won't update the summary workbook unless open?

Thanks

Chock

**:) Sixthsense :)** · 06-04-2013, 07:23 AM

Yes reference to table needs the file to be kept open. One suggestion instead of table reference convert it to cell/range reference to get rid of this issue.

**ChocksterNo1** · 06-04-2013, 07:57 AM

thanks, yes swapping the reference from table to cell/range works.

Many thanks

Chock

**TallorTech** · 08-17-2015, 11:45 AM

SixthSense,

You're a hero. I just spent several hours working on my massive sumifs formula that referenced different sharepoint spreadsheets. I appreciate the insight!!