Links to other worksheet not updated or showing #VALUE!

[jaysan3]

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

 

[Fred Smith]

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.

 

[Jacob Skaria]

Unless you use an Add-In; these formulas will work only if all external data
sources are open.

If this post helps click Yes

 

[jaysan3]

Errrm... where & how do i use the Add-in?

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

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

 

[jaysan3]

Changing the date doesnt help.

My mistake, the links are in a different workbook, not worksheet as
mentioned earlier.

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.

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

 

[Jacob Skaria]

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria

Errrm... where & how do i use the Add-in?

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

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

 

[jaysan3]

Unable to download from site. Any other options?

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria

Errrm... where & how do i use the Add-in?

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


:

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

 

[Jacob Skaria]

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria

Unable to download from site. Any other options?

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[jaysan3]

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.

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria

Unable to download from site. Any other options?

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[Jacob Skaria]

'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

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.

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria

Unable to download from site. Any other options?

:

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[jaysan3]

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?

'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

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.

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria


:

Unable to download from site. Any other options?

:

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[Jacob Skaria]

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

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?

'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

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.

:

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria


:

Unable to download from site. Any other options?

:

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[jaysan3]

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.

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

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?

'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


:

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.

:

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria


:

Unable to download from site. Any other options?

:

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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

 

[Jacob Skaria]

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

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.

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

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?

:

'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


:

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.

:

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$10000>DATE(2009,1,1)))


If this post helps click Yes
---------------
Jacob Skaria


:

Unable to download from site. Any other options?

:

Check out the below link...and download.

http://xcell05.free.fr/morefunc/english/index.htm

If this post helps click Yes
---------------
Jacob Skaria


:

Errrm... where & how do i use the Add-in?

:

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


:

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