| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links to other worksheet not updated or showing #VALUE!
I have a formula linking to another worksheet but it does not show the result
unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Your problem is that Excel does not recognize "01/01/09" as a date. It
thinks you are looking specifically for that text string. Do it this way: =countifs(...,""&date(2009,1,1)) Regards, Fred. "jaysan3" wrote in message ... I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Unless you use an Add-In; these formulas will work only if all external data
sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Errrm... where & how do i use the Add-in?
"Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Changing the date doesnt help.
My mistake, the links are in a different workbook, not worksheet as mentioned earlier. "Fred Smith" wrote: Your problem is that Excel does not recognize "01/01/09" as a date. It thinks you are looking specifically for that text string. Do it this way: =countifs(...,""&date(2009,1,1)) Regards, Fred. "jaysan3" wrote in message ... I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Check out the below link...and download.
http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Unable to download from site. Any other options?
"Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Try the SUMPRODUCT() version of the formula and feedback
=SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Thanks, Jacob.
The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
'Try this in a sample workbook..
'You can add more conditions like =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000=DATE(2009,1,1)), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000<=DATE(2009,6,30))) 'You can also find the average..the below way. The below will average the values in column B if the values in the corresponding colA = "a" '=AVERAGE(IF('C:\[extTest.xls]Sheet1'!$A$1:$A$100="a",'C:\[extTest.xls]Sheet1'!$B$1:$B$100)) 'PS: I am currently using 2003 and so cannot try out the same in 2007 . If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Thanks, Jacob. The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Ahh I see. Anyway, the results does not come up the same if i use sumproduct
in your 1st example. Any idea? Also, what does the '--' mean? "Jacob Skaria" wrote: 'Try this in a sample workbook.. 'You can add more conditions like =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000=DATE(2009,1,1)), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000<=DATE(2009,6,30))) 'You can also find the average..the below way. The below will average the values in column B if the values in the corresponding colA = "a" '=AVERAGE(IF('C:\[extTest.xls]Sheet1'!$A$1:$A$100="a",'C:\[extTest.xls]Sheet1'!$B$1:$B$100)) 'PS: I am currently using 2003 and so cannot try out the same in 2007 . If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Thanks, Jacob. The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
Are you sure..As mentioned in my previous post have you tried this with a
fresh sample...(probably with a smaller piece of data) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Ahh I see. Anyway, the results does not come up the same if i use sumproduct in your 1st example. Any idea? Also, what does the '--' mean? "Jacob Skaria" wrote: 'Try this in a sample workbook.. 'You can add more conditions like =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000=DATE(2009,1,1)), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000<=DATE(2009,6,30))) 'You can also find the average..the below way. The below will average the values in column B if the values in the corresponding colA = "a" '=AVERAGE(IF('C:\[extTest.xls]Sheet1'!$A$1:$A$100="a",'C:\[extTest.xls]Sheet1'!$B$1:$B$100)) 'PS: I am currently using 2003 and so cannot try out the same in 2007 . If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Thanks, Jacob. The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
I have checked the raw data & found the discrepency. In the date column,
there are a few entries which are 'rejected' or 'cancelled' and are still calculated when using DATE function. "Jacob Skaria" wrote: Are you sure..As mentioned in my previous post have you tried this with a fresh sample...(probably with a smaller piece of data) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Ahh I see. Anyway, the results does not come up the same if i use sumproduct in your 1st example. Any idea? Also, what does the '--' mean? "Jacob Skaria" wrote: 'Try this in a sample workbook.. 'You can add more conditions like =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000=DATE(2009,1,1)), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000<=DATE(2009,6,30))) 'You can also find the average..the below way. The below will average the values in column B if the values in the corresponding colA = "a" '=AVERAGE(IF('C:\[extTest.xls]Sheet1'!$A$1:$A$100="a",'C:\[extTest.xls]Sheet1'!$B$1:$B$100)) 'PS: I am currently using 2003 and so cannot try out the same in 2007 . If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Thanks, Jacob. The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Links to other worksheet not updated or showing #VALUE!
OK. Post sample data may be 10 row of data with 2 or 3 columns,,and let us
work on that. Post the formula you have tried, actual results and the expected results If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have checked the raw data & found the discrepency. In the date column, there are a few entries which are 'rejected' or 'cancelled' and are still calculated when using DATE function. "Jacob Skaria" wrote: Are you sure..As mentioned in my previous post have you tried this with a fresh sample...(probably with a smaller piece of data) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Ahh I see. Anyway, the results does not come up the same if i use sumproduct in your 1st example. Any idea? Also, what does the '--' mean? "Jacob Skaria" wrote: 'Try this in a sample workbook.. 'You can add more conditions like =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000=DATE(2009,1,1)), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000<=DATE(2009,6,30))) 'You can also find the average..the below way. The below will average the values in column B if the values in the corresponding colA = "a" '=AVERAGE(IF('C:\[extTest.xls]Sheet1'!$A$1:$A$100="a",'C:\[extTest.xls]Sheet1'!$B$1:$B$100)) 'PS: I am currently using 2003 and so cannot try out the same in 2007 . If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Thanks, Jacob. The sumproduct does show updated data, but the value is different compared to countif. BTW, I also need to count data between a specified date and use averageifs in the same way as well. "Jacob Skaria" wrote: Try the SUMPRODUCT() version of the formula and feedback =SUMPRODUCT(--('D:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000=$A33), --('D:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000DATE(2009,1,1))) If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Unable to download from site. Any other options? "Jacob Skaria" wrote: Check out the below link...and download. http://xcell05.free.fr/morefunc/english/index.htm If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: Errrm... where & how do i use the Add-in? "Jacob Skaria" wrote: Unless you use an Add-In; these formulas will work only if all external data sources are open. If this post helps click Yes --------------- Jacob Skaria "jaysan3" wrote: I have a formula linking to another worksheet but it does not show the result unless I open that worksheet too. Any ideas? Please correct me if my formula or excel settings is wrong. =COUNTIFS('d:\[Outgoing List 2009.xlsm]Outgoing'!$J$2:$J$10000,$A33,'d:\[Outgoing List 2009.xlsm]Outgoing'!$T$2:$T$10000,"01/01/09") When I tried a simple formula, it works fine. =d:\[Outgoing List 2009.xlsm]Report'!$B3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Showing when a file was updated | Excel Discussion (Misc queries) | |||
| This worksheet contains links that cannot be updated | Excel Discussion (Misc queries) | |||
| Links not updated when primary worksheet is sorted | Excel Worksheet Functions | |||
| Showing Updated Cells | Excel Discussion (Misc queries) | |||
| Links not showing updated value | Excel Discussion (Misc queries) | |||
Linear Mode
