#VALUE error when linking to other files

I have a summary sheet that pulls info from approx 10 other excel
files.

In one column of the summary sheet, the links perform just fine.  Each
cell in this column is the sum of an entire column in another
workbook.  When I open the summary page and choose "yes" to update
with new information, everything works properly.

However, in the same worksheet as this, I have another column where
each cell is the result of a SUMIF where the data comes from another
workbook.  The odd part that I cannot understand is why when I open
the summary sheet and update the information do all the cells in that
column get a #VALUE error, while the other column that references the
same workbook comes out properly?

And the real kicker is that as soon soon as I individually open each
referenced workbook, the #VALUE errors fill in with the correct
information.

What is going on here?

Thanks!

0
rbk (1)
4/7/2005 8:32:37 PM
excel 39879 articles. 2 followers. Follow

1 Replies
438 Views

Similar Articles

[PageSpeed] 46

There are some functions that don't work with closed workbooks.  =sumif() is one
of them.

But there are workarounds.

Saved from a previous post:

Use a different function...

Maybe =sum(if(...)) or =sumproduct() will work ok for you

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)

If this didn't help, post back with your existing formula.

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.)

"Jerome81 - ExcelForums.com" wrote:
> 
> I have a summary sheet that pulls info from approx 10 other excel
> files.
> 
> In one column of the summary sheet, the links perform just fine.  Each
> cell in this column is the sum of an entire column in another
> workbook.  When I open the summary page and choose "yes" to update
> with new information, everything works properly.
> 
> However, in the same worksheet as this, I have another column where
> each cell is the result of a SUMIF where the data comes from another
> workbook.  The odd part that I cannot understand is why when I open
> the summary sheet and update the information do all the cells in that
> column get a #VALUE error, while the other column that references the
> same workbook comes out properly?
> 
> And the real kicker is that as soon soon as I individually open each
> referenced workbook, the #VALUE errors fill in with the correct
> information.
> 
> What is going on here?
> 
> Thanks!

-- 

Dave Peterson
0
ec357201 (5290)
4/8/2005 1:46:00 AM
Reply:

Similar Artilces:

Error: Invalid byte was found at byte index 63.
Does anyone know what this means: "Invalid byte was found at byte index 63. " If yes, please help. Apogee Apogee wrote: > Does anyone know what this means: > > "Invalid byte was found at byte index 63. " That means exactly what it says: At index 63 XML parser found a byte, which is invalid either according document's encoding or is forbidden in XML documents at all (see list of allowed in XML characters at http://www.w3.org/TR/2000/REC-xml-20001006#charsets) -- Oleg Tkachenko http://www.tkachenko.com/blog Multiconn Technologies, Israel ...

XCH Error 3092, OAB replication
I am getting error 3092 for OAB in Exchnage 2003 (migrated from 5.5) "Error 1129 occurred while processing a replication event. Folder: (3-8) NON_IPM_SUBTREE\OFFLINE ADDRESS BOOK\EX:/o" Tried to delete offilne addressbook and recreate but error has not stopped. Any help will be a great help on where to look to get rid of this issue. Thanks ...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Invalid XML error when I open customization setting
I have a problem when I try to open customization setting after I import an entity. The system errored "Invalid XML" "The XML passed to the platform is not well-formed XML". Please recommend how to resolve this problem. Thanks. ...

Linker Error after upgrade from VC7.1
Hello all, After upgrading a VC7.1 project to visual studio 2005, it failed to build in the release configuration with the follwoing error : 1>nafxcw.lib(winocc.obj) : error LNK2005: "public: class CWnd * __thiscall CWnd::GetDlgItem(int)const " (?GetDlgItem@CWnd@@QBEPAV1@H@Z) already defined in InstallDlg.obj The debug build works fine. The project uses MFC in a static library. Well, after doing some research, it seems that this one is tied to the fact that in a release configuration, _AFX_ENABLE_INLINES is defined, so inline functions are embedded in the .obj file. Sure there...

Where is the workspace file stored?
I was given a project folder containing several dozen files. So where is the workspace file normally located? If I don't have a workspace file how can I cerate one? Using VC version 6. TIA I haven't used VC6 for some time, but if I remember right you can just open the .dsp file and it will create a .dsw file for you. It typically goes in the same folder with the code. Tom "Phisherman" <noone@nobody.com> wrote in message news:be93539ccf8a6f25ibh4vt6ggmtbi024mq@4ax.com... >I was given a project folder containing several dozen files. So where > is the wor...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

cant start outlook pst file to large
Hi I hope somebody may be able to help im new to outlook and now I cant start outlook message says cannot open your default email folders pst file its too large as I cannot open outlook I cannot delete old folders See if this helps: http://support.microsoft.com/kb/296088 -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Ray" <Ray@discussions.microsoft.com> wrote in message news:B3B824DC-3C9B-4B1B-8EF3-61...

Drag and Drop file/directory names on Tree Control.
hi, I am writing an application in which i have one Tree control. Now i want that , when user drag & drop any file/folder on tree control the file name/folder name should be add in the tree control item. User may drag and drop file from desktop or may be from directory. I just want the path of that file/foder name nothing else. so plz tell me will it be possible ... Plz Help me............. Klic..... > I am writing an application in which i have one Tree control. >Now i want that , when user drag & drop any file/folder on tree >control the file nam...

Trying to read XML file into dataset
Hello. Developing a web site in VS2005, SP1, VB, .NET 2.0, ASP.NET 2.0 on XP Pro, SP2. I eventually want to use a gridview table to view xml data, but right now i can't seem to get my xml data into a dataset so that i can use it. I have an XML file and can open it in IE. There is no XSD or XSL file to go with it. I can't seem to get the XML data into a dataset. I tried using the following code, but it just does nothing. It doesn't give an error. It just executes the code and does nothing. Dim dataSet As DataSet = New DataSet dataSet.ReadXml("...

Linking information form one worksheet to another
Hi, i have a big problem i want to create a link between some columns in two seperate worksheets, so that when i type a name on the master sheet it would give me the required information.Dont know if i explained this right, lets say on the master sheet i have columns : Name,Sex and Height on columns A,D and BH respectively i want that if i type a person's name in worksheet 2 it should give me the results on columns A,C,F in that worksheet. Please help i need it asap for my director One way .. Assuming your "master" sheet is named: Master, with data in row1 down In Shee...

error on upgrade: ID 4386
Hello, I am upgrading from DPM 2010 RC to DPM 2010 RTM, 64 bit version, on Win 2008 R2 standard. The RC is working without any issues. The upgrade scenario is supported. At the very end of the process the upgrade fails giving me the following. _____ The SQL Server installation failed because a restart was pending on this computer. Restart the computer and then start DPM Setup again. ID: 4386. Details: Unknown error (0x84be0bc2) _____ Restarting the server does not correct the issue. On the next attempt I am getting the same error message. Where to look for the pending rest...

error 0x800cc0f
i installed windows xp, and i set up all my email accounts. they are all working , except one: i receive 0x800cc0f message, which states that the service has been interrupted, contact your ISP...., but this is not the case, since my internet conneciton is working fine all the account settings are correct I am having the same problem. I have to close outlook and reopen to retrieve all of my messages. Have you found a resolution yet? "Kerstin" wrote: > i installed windows xp, and i set up all my email > accounts. they are all working , except one: i receive > 0x800c...

opening .bak file
Hi, I hope someone can help! I recently was performing a compacting operation on my outlook express folders. My computer shutdown due to a power failure and I lost some emails from a few of my folders. I was however able to get the .bak files (of the .dbx files that were lost) out of the recycle bin. If I could open one of these .bak files or convert it back to the .dbx file from which it was created I would retrieve these emails. Can anyone instruct me as to open this .bak file? Thanks for any help! Arnie K. How did you get the bak files out of the Recycle Bin? All e...

why are links to websites (inserted in publisher documents) disab.
why are links to websites and e-mailadresses (inserted in publisher documents) still working in emailpreview but disabled in webpage preview? ...

DNS Error on Exchange
I am getting the following error on my Exchange server. What is my problem? Thanks. Event Type: Warning Event Source: smtpsvc Event Category: None Event ID: 2012 Date: 1/28/2005 Time: 12:01:53 PM User: N/A Computer: PUMA Description: SMTP could not connect to the DNS server '24.220.0.11'. The protocol used was 'UDP'. It may be down or inaccessible. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: d5 04 00 00 Õ... Is that a DNS server that's configured on your Exchange box? It shoul...

outputting values to a range from one formula
I wish to generate a table automatically by means of a single formula that applies an iteration on a starting given value with a given step, and the computed values are posted/entered automtically into cells from a given cell onwards, say below it, until the computed value reaches a certain given limt. This is somehow the inverse of INDIRECT or of OFFSET. These can pull values from a variable addresses of cells. What I need is to push values into a variable addresses of cells. Can anyone help me on that?? Thanks. :confused -- Shafe ---------------------------------------------------------...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

Please Help w/ Large Excel File
Hello everyone. I have this Excel file that has drawing text box objects in the document. When I delete a text box the attributes still are present, but just not showing. It is continually growing my file size. When I export the document to HTML after deleting all the text box drawing objects and saving the file, the text box objects still show as vtextbox elements in this file. I tried to reproduce this with a new Excel workbook but could not. I would be greatful if anyone can figure this out, as I have a lot of documents to re-create if there is no solution. Thank you. exce...

Recording Macro Error Message
While recording the macro, I access the cell I need the formula in and write the formula. After writing the formula and hitting ENTER, I receive a microsoft GUI with "Unable To Record", and ackknowledge the GUI by hitting OK. The forluma is written to the cell - all even has the desired result/calculation. When I access the code, the navigation within the worksheet is listed in the code, but the furmula is not listed. I tried exiting the file and trying a second time, yet the same results. How do I "reset" the macro recorder to again begin to record all as...

Installation Error Message
I am attemting to install CRM4 or W2003 server running SQL 2008. I get the following message: Setup requires SQL Server Reporting Services 2005 Hotfix for role creation on SQL Reporting Service SP2 Workgroup edition at the specified Reporting Services Report Server http://XXXXX/reportserver. It is asking for SQL2005 hotfix yet I am running SQl2008. Any suggestions? ...

Error After Customizing 'Accounts' Form
Hello. Earlier today, I created an onSave event along with some other customizations for a tab in the 'Accounts' form. After becoming comfortable with my changes, I published the form and tested with a small Account that we do not access much. One of my fields was planned to be read-only, but I had it writable for a test. When I typed a value into the field and saved, I received an error and I can no longer open the specific account. I found the problem with the custom field and fixed it. However, I am still unable to open that one account. Here is the error I received: ...