| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
SUMIF Returns a #VALUE error when external source is closed
Can you help me out with this? if works fine if the external source is open but returns a #value if its closed. is there anyway of correcting this so that it will pull from the external when it is closed? -- ghynes ------------------------------------------------------------------------ ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587 View this thread: http://www.excelforum.com/showthread...hreadid=398561 |
|
#2
|
|||
|
|||
|
No, the formula requires the second file to be open.
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ghynes" wrote in message ... Can you help me out with this? if works fine if the external source is open but returns a #value if its closed. is there anyway of correcting this so that it will pull from the external when it is closed? -- ghynes ------------------------------------------------------------------------ ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587 View this thread: http://www.excelforum.com/showthread...hreadid=398561 |
|
#3
|
|||
|
|||
|
You could use a different function:
=sumproduct() will work with closed workbooks: =SUMPRODUCT(--('[book1.xls]sheet1'!$A$1:$A$10=A1), '[book1.xls]sheet1'!$b$1:$b10) As an example. ghynes wrote: Can you help me out with this? if works fine if the external source is open but returns a #value if its closed. is there anyway of correcting this so that it will pull from the external when it is closed? -- ghynes ------------------------------------------------------------------------ ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587 View this thread: http://www.excelforum.com/showthread...hreadid=398561 -- Dave Peterson |
|
#4
|
|||
|
|||
|
Try SUMPRODUCT instead... =SUMPRODUCT(--('C:\Path\[FileName.xls]Sheet1'!$A$1:$A$100="Criteria"),'C:\Path\[FileName.xls]Sheet1'!$B$1:$B$100) Note that if the criteria is a numerical value, remove the quotes. Hope this helps! ghynes Wrote: Can you help me out with this? if works fine if the external source is open but returns a #value if its closed. is there anyway of correcting this so that it will pull from the external when it is closed? -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=398561 |
|
#5
|
|||
|
|||
|
nice one. that works. thanks. -- ghynes ------------------------------------------------------------------------ ghynes's Profile: http://www.excelforum.com/member.php...o&userid=26587 View this thread: http://www.excelforum.com/showthread...hreadid=398561 |
|
#6
|
|||
|
|||
|
that works. cool. thanks.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions | |||
| inserting rows through external data source | Excel Discussion (Misc queries) | |||
| SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions | |||
| Sumif range returns #NUM! | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
