#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
378 Views

Similar Articles

[PageSpeed] 39

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:

Quirky recent file list #2
Debra--- I do tend to open from Windows Explore more then File>Open. But I am saving the file. It's more that it is annoying when I close a file by accident and don't quite remember where it got saved on the server. Stacie -- SPenney ------------------------------------------------------------------------ SPenney's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=267592 Well, I guess you weren't the only one who wanted this behaviour changed. <g> If/when you upgr...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

Linking a graph to a csv file
**Excel 2002 Hello, I'm sure this is exceedingly easy, but somehow, I'm screwing this up. I ultimately need to have a self- updating PowerPoint file (client mandates a ppt file). According to ppt's help, that means I have to use linked Excel graphs, not embedded ppt graphs. However, my programmer will be supplying me with tons of csv files. When I try to create an excel file with a data link to a csv file, it tells me that it can't update it unless I open the source file. I *can't* take the time to open each csv - there will be hundreds. What am I missing wi...

re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...

Value of Less Than Zero to Equal Zero
Hello: I am trying to format my answers in cells of a worksheet. I have an equation that uses addition, subtraction, multiplication and division. This equation has dependent variables in other cells and when these cells are filled in with data, the original equation yields a number. Unfortunately, if one of the cells is not used, there is still a value reported by the original equation. As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the result will be equal to 5.5. But if cell A2 does not have a value, the value will report -0.5. Here is the problem. Well, I think I have t...

Datagridview Combobox Values
Good Afternoon All, I have a datagridview dgv1 that has a combobox column in it. Each row should have an individual item to select from in the combobox. My code displays the same values in all of them. Any help would be great! Here's my code: Dim dsMP As DataSet = New DataSet() Dim intRow As Integer = 0 Dim intRows As Integer = dgvRO.Rows.Count Dim strPartNumber As String = "" Dim dt As DataTable = Nothing Dim dr As DataRow = Nothing For intRow = 0 To intRows - 1 strPartNumber = dgvRO.Rows...

Max Value
I have a sent worksheets that supply data. HOwever I would like to set up a VLookup that looks by Month and Max Value. For Example Column A is Jan, Feb, March, Feb, Jan, Mar and Column B is 42, 18, 22, 38, 45, 11. I want to look for particular month and highest value in that month How about this: =MAX(IF(A1:A30="Jan",B1:B30)) 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.) (and adjust the ranges accordingly) Ramon wrote: > > I have a sent wor...

Import Palm files
Can anyone help me by telling me how to import palm files (dat files) into outlook. I want to import contact, calendar, etc Depends what you can export to from Palm Desktop. Personally, I've found this messy. Why not just get sync software that can sync your Palm with Outlook directly? Chapura Pocketmirror or Pumatech Intellisync are two ideas....or your Palm CD may have come with this option. F. Carvalho wrote: > Can anyone help me by telling me how to import palm files > (dat files) into outlook. I want to import contact, > calendar, etc ...

outlook 2000 help file
Seems after I did added SP3 from MS upgrade site my help from outlook has quit working. message is msohelp caused error in itss.dll. help works in ALL other programs including outlook address book. I'm running windows 98 SE, Office 2000 SB and IE 6. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.542 / Virus Database: 336 - Release Date: 11/18/03 ...

ESM error
I'm hoping someone can help me with this. I have Exchange2k3 SP2 running on a Win2k3 server. Our Exchange Admin is out on medical leave so I am resuming his responsibilities. My problem is I cannot access some of the storage groups using exchange system manager. We have 4 mailbox stores and I get an error trying to access 3 of them. The error is "An unknown error has occurred. ID no: 80040d1b Exchange System Manager". What is really puzzling is if I click on a storage group a second time, I don't get the error but the storage group shows up as empty. I have thi...

#Error Message
I have created a calculation to count the number of people and placed it in the Name Footer: =count([EyNumber]) and called the unbound box, CountNames. When there are no results it returns the #Error message. How can I remove this error message and display a message like, "No Data", or display the true results of a count? I have tried using the IsError function but can't seem to get it to work. Thanks Hi Nigel, Try: =IIf(IsError([EyNumber]), "No Data", Count([EyNumber]) Clifford Bass Nigel wrote: >I have created a calculatio...

How do I add a value to a cell every 30 days?
I'm trying to create a calculator using Excel where in I add a given value to a cell every month. This is not a financial equation. One way: assume you want to add 10 every month, starting one month from today: =DATEDIF("2/11/2005",TODAY(),"m") * 10 or, every 30 days: =INT((TODAY()-DATE(2005,2,11))/30)*10 In article <BC7BB5B0-A994-4ABC-8200-8E198C51C3E1@microsoft.com>, "ZZ430" <ZZ430@discussions.microsoft.com> wrote: > I'm trying to create a calculator using Excel where in I add a given value to > a cell every month. T...

List Shows Private Files
DID ANYONE ELSE NOTICE THIS? Whenever I save an attachment, and go to the drop down menu for file name, I am shown a list of dozens of files recently accessed on the computer (not just files accessed by Outlook Express). This occurs even if recent cocuments is turned off in Windows XP and even if the recent documents folder for Windows XP has been cleared. This shows too much! Others using Outlook Express on the same computer can potentially view the file names of every document, image, and device recently accessed. Where is the folder to clear this data out of? duncan <anonym...

Memory Usage When Playing WMV HD Files
Hi all, When I watch a WMV HD file using WMP11, the file appears to start up and play well until I try to fast forward / jump to another part of the file, then the player just stops and after watching via the task manager, WMP memory usage goes through the roof ie. gigabytes and ultimately the player crashes. Have reinstalled the player, have tried various codecs etc but all appears to be unsuccessful. System is XP based, E6600 cpu with 4gb PC6400 memory and 8800GTS 640mb graphics. Any help would be much appreciated. Thanks in advance. Dumb question: what video card d...

Debugger comes up when attaching files to Outlook 2002
I have a pro bono non-profit client running win xp sp3 & ms office pro. Last week she reported that every time she tried to attach a file to an outlook email, she received a dialog asking her if she wanted to start the debugger. It never allows her to attach the file and if she goes through with starting the debugger, she gets a variety of errors... never a repeating error. Anyone have any experience with this error? Michael ...

Keeping rows together after link
I have spreadsheet that has several columns that link to anothe spreadsheet.This is G1:P100. My A,B,C,D columns is where I pu information related to the information in G-P. For instance, G-P link to a students information. It changes all the time. In A-D I hav columns to input criteria for each student. The problem is that whe the link updates the criteria and the student info end up on diffren rows now. For instance If B1 is " Has Housing" and G1 is " Steve" afte the update " Steve" might move to G3 and the "Has Housing" does no move with it -- Message...

To increase the upload file limit ONLY for one site collection
Hi, I have more than 1900 site collections on a web application. I know I can modify the upload file limit thru the CA or directly on the Web config file. However, I do not want to change the setting for all the site collections. I need to be able to change the setting only for a particular site collection. Is there anyway I can add manualy that entry in the web config file. Has anyone tried? Is this possible? Thanks in advance, Antonio ...

svhost.exe: A CRM-related File?
Hello all. McAfee VirusScan has suddenly notified me that it detected a "potentially unwanted file" on the machine that I am using to host an ActionPack version of Microsoft CRM. This is an SBS installation, with everything running on one test machine. Here is the warning from McAfee: ---------------- Potential Unwanted Program Detected! The file C:\WINNT\System32\svhost.exe is a Potentially Unwanted Program (ServU-Daemon) ---------------- When I try to move the file to the desktop, I am alerted that it is in use and cannot be moved (no surprise, huh?). The interesting thin...

Combo Box Value List/Query Criteria
Hi Folks - I have a combo box with a value list of Option1, Option2 and ALL. I pass this combo box info into a query. Here's a sample of the query criteria: IIf([Forms]![frmSwitchboard]![cboCaseType]="all",Is Null,[Forms]![frmSwitchboard]![cboCaseType]) In other words, if the combo box selection is ALL, then display all records, otherwise use selected option. The above criteria does not work. Any suggestions? Thanks. Michael Michael, Try... (IsNull is an operator, not a value) IIf([Forms]![frmSwitchboard]![cboCaseType]="All", Null,[Forms]![frmSwitchboar...

Opening Files #5
When I first open Excel ( Office 2003) it goes to the wrong file. How can I get it to open intially the file I want ? Thank You -- George E To set up your main file in XL: <Tools> <Options <General> tab, And in the "Default File Location" box, Enter the path to whatever drive, and directory and file that you want. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "George E...

file sbolext.dll
Downloaded a program some time ago that I found had spyware in it.When I took out the programs I started getting error message at start up of Outlook. "file hotbar\bin\448~1.o\sbolext.dll missing" Downloaded from Microsoft the newest version of the file. Still getting the error message on start. The program runs fine, don't know why it is looking for this file. Running Office 2000 Sr-1. The program removed was hot bars for wall paper and scam mail. Any help would be appreciated. Thank you. e-mail address is jvanwie@tampabay.rr.com. ...

i renamed a file i needed and lost the info how do i get it back?
I had 2 files with the same name in excel. After I excited excel, I wanted to rename my new file, and I thought I was renaming the new file, but I actually renamed the old file I was working in and when it said this file exists do you want to change I said yes. I lost all my work. How do I get it back? You can't unless you had a backup. Not nice to excite excel. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Tera" <Tera@discussions.microsoft.com> wrote in message news:6545DD51-3597-45DB-A187-F248ED970C8F@microsoft.com... >I had 2 files with the same n...

popup box message; "Publisher cannot open the file"
saved as a .pub file but unable to open. popup box message "Publisher cannot open the file" What version Publisher do you have? Do you know what version Publisher was used to create the file? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "stature" <stature@discussions.microsoft.com> wrote in message news:0AD1D565-743D-478D-95DD-5C5358400272@microsoft.com... > saved as a .pub file but unable to open. popup box message "Publisher cannot > open the file" ...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Batch convert XP files to 2000?
I need to convert several hundred Excel XP files to Excel 2000 format. Is there a batch converter available for free? Thanks. The file system is the same - Have you tried just opening them in 2000? You will only suffer if you have used any features in the files that were not available in 2000, eg Coloured Tabs (Don't laugh - I like em!!). Other than that, there is no batch converter that I know of, but there is simply no better way than trying. The following link to a review by John Walkenbach, which will give you an overview of the differences between 2000 and 2002 should give yo...