I have a workbook where the functions reference a different workbook; why
does it take soooo long for my worksheet to update?

Also is there a way to simplify the formula below?

Example:
workbook 1 - LOG.xls
Part # Inspection Date Rejection 1 Rejection 2 Rejection 3
Rejection 4
5670 1/9/2009 Documents Application
5671 1/15/2009
5672 2/3/2009 Testing Documents
5673 2/25/2009 Applcation Testing Documents
Identification

workbook 2 - REJECTIONS.xls
# of Document Rejections in Jan. 2009 =CURRENT FORMULA (see below)
# of Application Rejections in Jan. 2009
# of Testing Rejections in Jan. 2009
and so on for each rejection type and month

CURRENT FORMULA:
=(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$C$2:$C$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$D$2:$D$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$E$2:$E$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$F$2:$F$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))

Keep in mind that anytime you link to an external file, you are essentially
running that file in the background. Depending on how many rows and how
complex the formula, you are going to see varying levels of performance.
Sometimes it helps to have the source file open. In cases of extreme lag, I
have copied both the source and target files on to my desktop and run the
process there to test the performance.
If you are running on an older system and selecting source files that are on
the network, you are going to see a lag. The formula you have here is going
to take a while to process.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Haydie-lady" wrote:

I have a workbook where the functions reference a different workbook; why
does it take soooo long for my worksheet to update?

Also is there a way to simplify the formula below?

Example:
workbook 1 - LOG.xls
Part # Inspection Date Rejection 1 Rejection 2 Rejection 3
Rejection 4
5670 1/9/2009 Documents Application
5671 1/15/2009
5672 2/3/2009 Testing Documents
5673 2/25/2009 Applcation Testing Documents
Identification

workbook 2 - REJECTIONS.xls
# of Document Rejections in Jan. 2009 =CURRENT FORMULA (see below)
# of Application Rejections in Jan. 2009
# of Testing Rejections in Jan. 2009
and so on for each rejection type and month

CURRENT FORMULA:
=(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$C$2:$C$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$D$2:$D$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$E$2:$E$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))+(SUMPRODUCT(--(LEFT('[LOG.xls]2009'!$F$2:$F$4)="D"),--(MONTH('[LOG.xls]2009'!$B$2:$B$4)=1),--(YEAR('[LOG.xls]2009'!$B$2:$B$4)=2009)))