SUMIF link
Posted by Tom Baker on February 08, 2002 10:36 AM
(1) I am using Excel 97 SR-1 on a local area network.
(2) I am using an automatic link to access information on a spreadsheet in a different directory using the following formula:
=SUMIF('P:\Executive\Budgets\2002 Budgets\[Victoria Budget 2002.xls]Salaries'!$O$6:$O$12,"S",'P:\Executive\Budgets\2002 Budgets\[Victoria Budget 2002.xls]Salaries'!$B$6:$B$12)/$A$60
(3) The formula works if the Victoria Budget 2002 spreadsheet is open but returns #VALUE! if it is not.
(4) I want it to work without having to open up the Victoria Budget 2002 spreadsheet. How do I do it?
(5) Other automatic links that I use ask, before allowing access to the spreadsheet, whether or not I want to update all linked information or keep the existing information. This one does not. It lets me right in and displays the #VALUE! in all cells that have the link in the formula.
The formula above appears as is when the Victoria Budget 2002 spreadsheet is not open. As soon as it opens the formula changes to:
=SUMIF('[Victoria Budget 2002.xls]Salaries'!$O$6:$O$12,"S",[Victoria Budget 2002.xls]Salaries'!$B$6:$B$12)/$A$60
I have never noticed a linked formula changing when the workbook to which it was linked was opened.
Perhaps SUMIF does not even work on a closed workbook.
I can't figure it out.
Posted by Dan Aragon on February 09, 2002 1:20 PM
Not sure if I can help, but what happens if you instead use 2 cells in your open workbook to reference the closed workbook cells in Victoria Budget 2002, then use the SUMIF formula and calculate based on the values of these new cells? Do you see what I mean? Although I don't really know why your method isn't working...maybe your right when you propose that Sumif doesn't work on closed workbooks? I've never tried it!
This might be a workaround though...
