A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Error result for link formula



 
 
Thread Tools Display Modes
  #1  
Old June 18th, 2009, 08:54 AM posted to microsoft.public.excel.worksheet.functions
TJ
external usenet poster
 
Posts: 184
Default Error result for link formula

Hi,

I got data as follows:
Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100


and I used formula
=SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material
(5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/
2008",'Material (5-1020)'!$H$14:$H$718)

to get the result:
Jan 09 Feb 09 total
Material 100 100 250
Labour 200 100 300

The total amount is linked to other report

The problem is when I open the file together with the report file to get the
link automatically, sometimes I found the result become error like double
duplication result as follows

Jan 09 Feb 09 total
Material 100 100 200
Labour 200 200 400

However the formula does not change at all.

Could anyone help me for this problem?



  #2  
Old June 18th, 2009, 09:27 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Error result for link formula

Try the below formula instead...

To get the total of materials for the month of January..with dates in ColA
and material amount in Column H..

=SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material
(5-1020)'!$H$14:$H$718)

To understanding the formula try with a different example in ColA and B....A
with dates and B with amounts....change the 1 to 2 for Februay...Or you can
even refer the month to a cell as '=MONTH(cell)'

=SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

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


"tj" wrote:

Hi,

I got data as follows:
Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100


and I used formula
=SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material
(5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/
2008",'Material (5-1020)'!$H$14:$H$718)

to get the result:
Jan 09 Feb 09 total
Material 100 100 250
Labour 200 100 300

The total amount is linked to other report

The problem is when I open the file together with the report file to get the
link automatically, sometimes I found the result become error like double
duplication result as follows

Jan 09 Feb 09 total
Material 100 100 200
Labour 200 200 400

However the formula does not change at all.

Could anyone help me for this problem?



  #3  
Old June 19th, 2009, 03:52 AM posted to microsoft.public.excel.worksheet.functions
TJ
external usenet poster
 
Posts: 184
Default Error result for link formula

Hi Jacob,

Thank you for the quick response

My next question is : can I use the same formula if the date contains of the
different year?

for example:

Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100
31/1/10 100
21/2/10 100

Regards,





"Jacob Skaria" wrote:

Try the below formula instead...

To get the total of materials for the month of January..with dates in ColA
and material amount in Column H..

=SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material
(5-1020)'!$H$14:$H$718)

To understanding the formula try with a different example in ColA and B....A
with dates and B with amounts....change the 1 to 2 for Februay...Or you can
even refer the month to a cell as '=MONTH(cell)'

=SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

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


"tj" wrote:

Hi,

I got data as follows:
Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100


and I used formula
=SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material
(5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/
2008",'Material (5-1020)'!$H$14:$H$718)

to get the result:
Jan 09 Feb 09 total
Material 100 100 250
Labour 200 100 300

The total amount is linked to other report

The problem is when I open the file together with the report file to get the
link automatically, sometimes I found the result become error like double
duplication result as follows

Jan 09 Feb 09 total
Material 100 100 200
Labour 200 200 400

However the formula does not change at all.

Could anyone help me for this problem?



  #4  
Old June 21st, 2009, 07:17 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Error result for link formula

Try this version...for Jan 2009. Change to suit your requirement...

(all in one line)
=SUMPRODUCT(--(TEXT('Material
(5-1020)'!$A$14:$A$718,"MMYYYY")="012009"),'Material (5-1020)'!$H$14:$H$718)

OR .......

=SUMPRODUCT(--(TEXT(A1:A10,"MMYYYY")="012009"),B1:B10)

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


"tj" wrote:

Hi Jacob,

Thank you for the quick response

My next question is : can I use the same formula if the date contains of the
different year?

for example:

Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100
31/1/10 100
21/2/10 100

Regards,





"Jacob Skaria" wrote:

Try the below formula instead...

To get the total of materials for the month of January..with dates in ColA
and material amount in Column H..

=SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material
(5-1020)'!$H$14:$H$718)

To understanding the formula try with a different example in ColA and B....A
with dates and B with amounts....change the 1 to 2 for Februay...Or you can
even refer the month to a cell as '=MONTH(cell)'

=SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

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


"tj" wrote:

Hi,

I got data as follows:
Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100


and I used formula
=SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material
(5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/
2008",'Material (5-1020)'!$H$14:$H$718)

to get the result:
Jan 09 Feb 09 total
Material 100 100 250
Labour 200 100 300

The total amount is linked to other report

The problem is when I open the file together with the report file to get the
link automatically, sometimes I found the result become error like double
duplication result as follows

Jan 09 Feb 09 total
Material 100 100 200
Labour 200 200 400

However the formula does not change at all.

Could anyone help me for this problem?



  #5  
Old June 22nd, 2009, 06:12 AM posted to microsoft.public.excel.worksheet.functions
TJ
external usenet poster
 
Posts: 184
Default Error result for link formula

Hi Jacob,

Yes it works, thanks a lot By the way could you please help me with the
SUMProduct fomula if we use more than 1 criteria except date criteria. The
table is:

Date Item Amount
1/1/09 Material - Sheet 100
2/2/09 Material - packing 150
12/2/09 Material - Sheet 100
31/3/09 Material - Other 100

I want the result :
Jan 09 Feb 09 March 09
Material - Sheet 100 100
Material - Packing 150
Material - Other 100

Thanks for your attention.

Regards,



"Jacob Skaria" wrote:

Try this version...for Jan 2009. Change to suit your requirement...

(all in one line)
=SUMPRODUCT(--(TEXT('Material
(5-1020)'!$A$14:$A$718,"MMYYYY")="012009"),'Material (5-1020)'!$H$14:$H$718)

OR .......

=SUMPRODUCT(--(TEXT(A1:A10,"MMYYYY")="012009"),B1:B10)

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


"tj" wrote:

Hi Jacob,

Thank you for the quick response

My next question is : can I use the same formula if the date contains of the
different year?

for example:

Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100
31/1/10 100
21/2/10 100

Regards,





"Jacob Skaria" wrote:

Try the below formula instead...

To get the total of materials for the month of January..with dates in ColA
and material amount in Column H..

=SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material
(5-1020)'!$H$14:$H$718)

To understanding the formula try with a different example in ColA and B....A
with dates and B with amounts....change the 1 to 2 for Februay...Or you can
even refer the month to a cell as '=MONTH(cell)'

=SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10)

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


"tj" wrote:

Hi,

I got data as follows:
Date Material ($) Labour ($)
1/1/09 100 -
22/1/09 - 150
2/2/09 200 100


and I used formula
=SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material
(5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/
2008",'Material (5-1020)'!$H$14:$H$718)

to get the result:
Jan 09 Feb 09 total
Material 100 100 250
Labour 200 100 300

The total amount is linked to other report

The problem is when I open the file together with the report file to get the
link automatically, sometimes I found the result become error like double
duplication result as follows

Jan 09 Feb 09 total
Material 100 100 200
Labour 200 200 400

However the formula does not change at all.

Could anyone help me for this problem?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.