Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick question on sumif
Hi, I have a file which use the sumif function pulling the data from
another file. The issue is that if the file where the sumif is pulling from is closed and I say filter the other file, i get the #VALUE error. I was told that the sumif function has to have the file open to see the data. I though it was an issue with permissions or something. Which is true? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick question on sumif
This may help:
http://www.mrexcel.com/forum/showthread.php?t=172225 Pete On May 25, 8:52*am, pat67 wrote: Hi, I have a file which use the sumif function pulling the data from another file. The issue is that if the file where the sumif is pulling from is closed and I say filter the other file, i get the #VALUE error. I was told that the sumif function has to have the file open to see the data. I though it was an issue with permissions or something. Which is true? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick question on sumif
On May 25, 6:52*am, pat67 wrote:
The issue is that if the file where the sumif is pulling from is closed and I say filter the other file, i get the #VALUE error. I was told that the sumif function has to have the file open to see the data. I though it was an issue with permissions or something. Which is true? What you were told. But why you would trust me any more than whoever gave you the correct explanation in the first place? Why don't you just create a simple test and see for yourself? Open a new workbook (Book1). In the same instance of Excel, open a second new workbook (Book2). In Book2, enter the formula =SUMIF([book1.xls]Sheet1!$A$1:$A$20,1). Save and close both workbooks, ideally first Book2, then Book1. Now open just Book2. If you get some warnings, just click OK. You will see that the SUMIF formula returns a #VALUE error. Obviously, that is not a permissions error because you created and saved both files. Now open Book1 in the same instance of Excel. Look at the SUMIF formula. It might __still__ returns a #VALUE error(!). In that case, select the SUMIF cell, press F2, then Enter to "re-enter" the SUMIF formula. It should now return a valid number, probably zero if you followed my instructions exactly. A key point is to open both workbooks in the __same__ instance of Excel. Normally that happens automagically when you click on the file icons. But if you changed the default configuration, that might open each file in separate instances of Excel. It is difficult to tell the difference visually, unless you are careful. To be sure, click File Open in Excel instead of clicking the file icons. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick question on sumif
On May 25, 11:47*am, joeu2004 wrote:
On May 25, 6:52*am, pat67 wrote: The issue is that if the file where the sumif is pulling from is closed and I say filter the other file, i get the #VALUE error. I was told that the sumif function has to have the file open to see the data. I though it was an issue with permissions or something. Which is true? What you were told. *But why you would trust me any more than whoever gave you the correct explanation in the first place? *Why don't you just create a simple test and see for yourself? Open a new workbook (Book1). *In the same instance of Excel, open a second new workbook (Book2). *In Book2, enter the formula =SUMIF([book1.xls]Sheet1!$A$1:$A$20,1). *Save and close both workbooks, ideally first Book2, then Book1. Now open just Book2. *If you get some warnings, just click OK. *You will see that the SUMIF formula returns a #VALUE error. *Obviously, that is not a permissions error because you created and saved both files. Now open Book1 in the same instance of Excel. *Look at the SUMIF formula. *It might __still__ returns a #VALUE error(!). *In that case, select the SUMIF cell, press F2, then Enter to "re-enter" the SUMIF formula. *It should now return a valid number, probably zero if you followed my instructions exactly. A key point is to open both workbooks in the __same__ instance of Excel. *Normally that happens automagically when you click on the file icons. *But if you changed the default configuration, that might open each file in separate instances of Excel. *It is difficult to tell the difference visually, unless you are careful. To be sure, click File Open in Excel instead of clicking the file icons. Try using SUMPRODUCT instead |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
quick question | Excel Discussion (Misc queries) | |||
Quick Question | Excel Discussion (Misc queries) | |||
quick question | Excel Discussion (Misc queries) | |||
Quick Question | Excel Worksheet Functions | |||
Quick Question | Excel Discussion (Misc queries) |