Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Linking between spreadsheets does not work unless I have both spr.
I am using Excel 2003 and I have upgraded from office 97. Before the upgrade
the spreadsheets that we were using here at work updated without a problem. Since the upgrade the main spreadsheet we use will not properly update unless all the spreadsheets that is gets information from are open. I go to the links and update and it give me the OK status. I set excel to maunally update. But no matter what I try everytime I update the main spreadsheet without all the others open it will not properly update the data. The odd part is that I cane created a new spreadsheet and I can get information off of those same spreadsheets untill I try to use a SUMIF or COUNTIF statement. |
#2
|
|||
|
|||
From what I've experienced with the newer versions of Excel is that SUMIF
won't pull information from a file that isn't open. I'm not sure on COUNTIF. For what I was doing I had to put the SUMIF's in the sheet I wanted to pull the information from and then use VLOOKUP so all the files didn't have to be open. "RCppe" wrote: I am using Excel 2003 and I have upgraded from office 97. Before the upgrade the spreadsheets that we were using here at work updated without a problem. Since the upgrade the main spreadsheet we use will not properly update unless all the spreadsheets that is gets information from are open. I go to the links and update and it give me the OK status. I set excel to maunally update. But no matter what I try everytime I update the main spreadsheet without all the others open it will not properly update the data. The odd part is that I cane created a new spreadsheet and I can get information off of those same spreadsheets untill I try to use a SUMIF or COUNTIF statement. |
#3
|
|||
|
|||
My bet is that the formulas didn't work in xl97, either (with the other
workbooks closed). But xl2002+ handle the links differently when it opens that workbook. In earlier versions, you could say "no" to the update links prompt and excel would continue to show the results of the previous calculation. xl2002+ shows the #ref! errors. You have a couple of choices. #1. Change the behavior of xl2002+ so that it behaves the way earlier versions of excel did. #2. Change your formula to something that works with closed workbooks. (My choice would be #2.) But for #1...Jim Rech posted a registry tweak: http://groups.google.com/groups?thre...GP11.phx .gbl If you choose #2, you can use the array formula =sum(if()) or even sumproduct. Saved from a previous post (maybe you'll see how you can modify your existing formula): {=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green", 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))} =sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"), 'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10) by the way, the =sum(if(... This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) === both of these formulas cannot use the whole column. RCppe wrote: I am using Excel 2003 and I have upgraded from office 97. Before the upgrade the spreadsheets that we were using here at work updated without a problem. Since the upgrade the main spreadsheet we use will not properly update unless all the spreadsheets that is gets information from are open. I go to the links and update and it give me the OK status. I set excel to maunally update. But no matter what I try everytime I update the main spreadsheet without all the others open it will not properly update the data. The odd part is that I cane created a new spreadsheet and I can get information off of those same spreadsheets untill I try to use a SUMIF or COUNTIF statement. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two spreadsheets to find differences. | Excel Discussion (Misc queries) | |||
Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Linking Workbooks | Excel Worksheet Functions | |||
Linking items GREATER THAN O on another worksheet in the same Work | Excel Discussion (Misc queries) | |||
Change Path names in copied work book | Excel Worksheet Functions |