linked cells will not update unless source file is open

**merlyn45** · 05-18-2012, 09:26 AM

Can anyone help me figure out why my workbook will not update unless the source file is open? I have no macros just formulas to index/match etc from the source file (Production Schedule, Plant schedule tab). Both files were 2003-07 format and converted to 2010. This file will be a major reference for other users including my boss and I dont want anyone viewing this file to have to open a source file from another directory. Both files are located within the same server drive, but within different folders. Here is the only formula that refers to the source if this helps:

When source file is open: =LOOKUP(9.9999E+307,CHOOSE({1,2},0,SUMPRODUCT(('[Production Schedule.xlsx]Plant Schedule'!$D$4:$D$220&'[Production Schedule.xlsx]Plant Schedule'!$E$4:$E$220=$C9)*(INDEX('[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220,1,MATCH(BQ5,'[Production Schedule.xlsx]Plant Schedule'!$N$1:$LM$1,0)):INDEX('[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220,ROWS('[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220),MATCH(BQ5,'[Production Schedule.xlsx]Plant Schedule'!$N$1:$LM$1,0)+23)<>""))*INDEX('[Production Schedule.xlsx]Plant Schedule'!$J$4:$J$220,MATCH(TRUE,INDEX('[Production Schedule.xlsx]Plant Schedule'!$D$4:$D$220&'[Production Schedule.xlsx]Plant Schedule'!$E$4:$E$220=$C9,0),0))))

When source file is closed: =LOOKUP(9.9999E+307,CHOOSE({1,2},0,SUMPRODUCT(('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$D$4:$D$220&'S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$E$4:$E$220=$C9)*(INDEX('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220,1,MATCH(BQ5,'S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$N$1:$LM$1,0)):INDEX('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220,ROWS('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$N$4:$LM$220),MATCH(BQ5,'S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$N$1:$LM$1,0)+23)<>""))*INDEX('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$J$4:$J$220,MATCH(TRUE,INDEX('S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$D$4:$D$220&'S:\Logistics\Production Schedules\[Production Schedule.xlsx]Plant Schedule'!$E$4:$E$220=$C9,0),0))))

**merlyn45** · 05-18-2012, 10:43 AM

Ok..ive looked up every resouce on the internet to figure this out but I still can't get it to work? Someone else out there in Excel Land must have the answer????

**merlyn45** · 05-18-2012, 12:33 PM

Is it possible that the function SUMPRODUCT does not link to closed source? Ive read that SUMIF is better used as SUM(IF)). If I change my function listed in my first post, how would I it look? SUM(PRODUCT(xxxxx) instead of SUMPRODUCT(xxxx)???

**merlyn45** · 05-18-2012, 01:29 PM

ok..just having a conversation with myself I guess...SUM(PRODUCT) does not work instead of SUMPRODUCT because SUMPRODUCT creates an array. The lack of replies to my post tells me that there is no solution.

**ron2k_1** · 05-18-2012, 01:45 PM

When you open the file you need to enable the content. By default, excel will not update them automatically. When the file is open, you'll see a little warning bar at the top of the sheet, it's going to have an "Options" button, if you press it, under links you can enable the option to update outside links.

Another way to update is to go to Data>Connections Section>Edit Links>Clink on the link source (Production Schedule.xlsx on your example) and click on Update Values.

**merlyn45** · 05-18-2012, 02:56 PM

Thanks for the reply, but I have tried both your suggestions and my destination file will not update unless the source file is open. I've been trying all day to find a way to make it work. (sigh)