Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
I have a workbook with a formulas referencing 5 other workbooks. Whenever I
sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
I guess what is happening is that, when the workbook with the formulas and
the workbook being reference are open together, the formula is =COUNTIF('[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5) but when just the formula sheet is open, the formula turns into =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\ [01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5) "Arlen" wrote: I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
COUNTIF won't work when referencing a closed file. Use SUMPRODUCT.
Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
Okay, Biff. One follow up.
If I can't select all of Column C, how can I at least select everything from C4 to Infinity? Like C4:C???? Arlen "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
OK, it sounds like you're not using Excel 2007...so:
C4:C65536 Note that SUMPRODUCT will evaluate *every* cell referenced. So it's in your best interest to use as small a range as is necessary. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... Okay, Biff. One follow up. If I can't select all of Column C, how can I at least select everything from C4 to Infinity? Like C4:C???? Arlen "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
Biff,
Perfect! But could you explain what is happening, because I'm gonna need to use it again with SUMIF and such. Thanks. "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
Let's look at a simple example:
...........A.......... 1........x........... 2..................... 3........x........... 4........z........... 5........y........... We need the count of "x". =SUMPRODUCT(--(A1:A5="x")) This expression will return an array of either TRUE or FALSE: (A1:A5="x") A1 = x = TRUE A2 = x = FALSE A3 = x = TRUE A4 = x = FALSE A5 = x = FALSE The end result of our formula is a sum but SUMPRODUCT can't sum those logical values. So, we need to convert them to numbers somehow. One way to do this is to use the double unary "--". This will coerce TRUE to 1 and FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0: --(A1=x) = 1 --(A2=x) = 0 --(A3=x) = 1 --(A4=x) = 0 --(A5=x) = 0 Then, SUMPRODUCT justs sums up the array: =SUMPRODUCT({1;0;1;0;0}) Result = 2 The SUMIF alternative works pretty much the same way except that when there are more than a single array (as in the above example) all the arrays are multiplied together to arrive at the result. Using the same sample from above: ...........A..........B 1........x...........5 2.....................2 3........x...........3 4........z...........1 5........y...........6 SUMIF column A = x: =SUMPRODUCT(--(A1:A5="x"),B1:B5) We still have our array of 1/0 with --(A1:A5="x") but now we introduced a 2nd array, column B, and these are the values we want to sum. Since the values in column B are already numeric numbers we don't need to "mess" with them. So, as I noted, when there is more than one array all the arrays get multiplied together like this: 1*5 = 5 0*2 = 0 1*3 = 3 0*1 = 0 0*6 = 0 SUMIF A = x, result = 8 See this for a comprehensive analysis of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Arlen" wrote in message ... Biff, Perfect! But could you explain what is happening, because I'm gonna need to use it again with SUMIF and such. Thanks. "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
Awesome!
Thank you for taking the time to explain that, Biff. Have a great day. Arlen "T. Valko" wrote: Let's look at a simple example: ...........A.......... 1........x........... 2..................... 3........x........... 4........z........... 5........y........... We need the count of "x". =SUMPRODUCT(--(A1:A5="x")) This expression will return an array of either TRUE or FALSE: (A1:A5="x") A1 = x = TRUE A2 = x = FALSE A3 = x = TRUE A4 = x = FALSE A5 = x = FALSE The end result of our formula is a sum but SUMPRODUCT can't sum those logical values. So, we need to convert them to numbers somehow. One way to do this is to use the double unary "--". This will coerce TRUE to 1 and FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0: --(A1=x) = 1 --(A2=x) = 0 --(A3=x) = 1 --(A4=x) = 0 --(A5=x) = 0 Then, SUMPRODUCT justs sums up the array: =SUMPRODUCT({1;0;1;0;0}) Result = 2 The SUMIF alternative works pretty much the same way except that when there are more than a single array (as in the above example) all the arrays are multiplied together to arrive at the result. Using the same sample from above: ...........A..........B 1........x...........5 2.....................2 3........x...........3 4........z...........1 5........y...........6 SUMIF column A = x: =SUMPRODUCT(--(A1:A5="x"),B1:B5) We still have our array of 1/0 with --(A1:A5="x") but now we introduced a 2nd array, column B, and these are the values we want to sum. Since the values in column B are already numeric numbers we don't need to "mess" with them. So, as I noted, when there is more than one array all the arrays get multiplied together like this: 1*5 = 5 0*2 = 0 1*3 = 3 0*1 = 0 0*6 = 0 SUMIF A = x, result = 8 See this for a comprehensive analysis of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Arlen" wrote in message ... Biff, Perfect! But could you explain what is happening, because I'm gonna need to use it again with SUMIF and such. Thanks. "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Value replaces formula result when file is opened. But why?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Arlen" wrote in message ... Awesome! Thank you for taking the time to explain that, Biff. Have a great day. Arlen "T. Valko" wrote: Let's look at a simple example: ...........A.......... 1........x........... 2..................... 3........x........... 4........z........... 5........y........... We need the count of "x". =SUMPRODUCT(--(A1:A5="x")) This expression will return an array of either TRUE or FALSE: (A1:A5="x") A1 = x = TRUE A2 = x = FALSE A3 = x = TRUE A4 = x = FALSE A5 = x = FALSE The end result of our formula is a sum but SUMPRODUCT can't sum those logical values. So, we need to convert them to numbers somehow. One way to do this is to use the double unary "--". This will coerce TRUE to 1 and FALSE to 0. So now our array of TRUE/FALSE is an array of 1/0: --(A1=x) = 1 --(A2=x) = 0 --(A3=x) = 1 --(A4=x) = 0 --(A5=x) = 0 Then, SUMPRODUCT justs sums up the array: =SUMPRODUCT({1;0;1;0;0}) Result = 2 The SUMIF alternative works pretty much the same way except that when there are more than a single array (as in the above example) all the arrays are multiplied together to arrive at the result. Using the same sample from above: ...........A..........B 1........x...........5 2.....................2 3........x...........3 4........z...........1 5........y...........6 SUMIF column A = x: =SUMPRODUCT(--(A1:A5="x"),B1:B5) We still have our array of 1/0 with --(A1:A5="x") but now we introduced a 2nd array, column B, and these are the values we want to sum. Since the values in column B are already numeric numbers we don't need to "mess" with them. So, as I noted, when there is more than one array all the arrays get multiplied together like this: 1*5 = 5 0*2 = 0 1*3 = 3 0*1 = 0 0*6 = 0 SUMIF A = x, result = 8 See this for a comprehensive analysis of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Arlen" wrote in message ... Biff, Perfect! But could you explain what is happening, because I'm gonna need to use it again with SUMIF and such. Thanks. "T. Valko" wrote: COUNTIF won't work when referencing a closed file. Use SUMPRODUCT. Have the source file open when you write the formula and then just use your mouse to point to the source. This way Excel will put all that path junk in for you. Much easier than typing it! =SUMPRODUCT(--(really_long_pathC1:C100=E5)) Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "Arlen" wrote in message ... I have a workbook with a formulas referencing 5 other workbooks. Whenever I sort the results, or close and open it, all results turn into #Value. The numbers come back when I reopen the workbook being referenced, but really, there must be something we can do? Here's one of those formulae: =COUNTIF('O:\Tacoma Dispatch\2007 Dispatch\Tacoma 2007\[01-08-07TacomaDispatch.xls]Tacoma'!$C:$C,E5), and Error Checking underlines everything, then just E5...so there. Also, is there a way to bypass the Update Links screen if the file hasn't been moved or renamed? I thank you for your time. Arlen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When was file last opened? | Excel Worksheet Functions | |||
message box when file is opened | Excel Worksheet Functions | |||
cannot open the excel file, the file is already opened | Excel Discussion (Misc queries) | |||
file opened twice on network | Excel Discussion (Misc queries) | |||
Copying the Editing in one file to Another opened XLS file | Excel Worksheet Functions |