thepr1nter
09-16-2005, 12:29 PM
Here's a challenge for you.
I started using the following formula to total expense in two different worksheets based on values in certain fields but things got out of hand when I had too many criteria to test for:
=SUM(IF((?NPS Detail '!$B$19:$B$1000=$C4)*(('NPS Detail'!$P$19:$P$1000="22c")+('NPS Detail'!$P$19:$P$1000="25c")),'NPS Detail'!$V$19:$V$1000,0))+SUM(IF(('P-Card '!$B$9:$B$710=$C4)*(('P-Card '!$I$9:$I$710="22c")+('P-Card '!$I$9:$I$710="25c")),'P-Card '!$N$9:$N$710,0))
( this formula is entered using the {CTRL + Shift + CR} )
So I graduated to this next formula which was easier in some ways but it's not transportable to a new workbook (as the last one was not either):
=DSUM('NPS Detail'!$A$19:$W$1000,"Exp.",Criteria!$A$2:$P$36)+DSUM('P-Card '!$A$9:$W$710,"Exp.",Criteria!$AA$2:$AO$36)
This one is a simpler but the criteria list gets out of hand and when I transport it to a new workbook it carries all the references to the original workbook with it.
So my idea is to create a function that I believe would copy to another workbook with ease. But I'm having trouble figuring out how to read through the rows of the worksheet and compare particular fields with criteria to determine if I need to add that row to the sum. I also tried to use the DSUM function in my UDF with no success.
Can anyone help point me in the right direction?
I started using the following formula to total expense in two different worksheets based on values in certain fields but things got out of hand when I had too many criteria to test for:
=SUM(IF((?NPS Detail '!$B$19:$B$1000=$C4)*(('NPS Detail'!$P$19:$P$1000="22c")+('NPS Detail'!$P$19:$P$1000="25c")),'NPS Detail'!$V$19:$V$1000,0))+SUM(IF(('P-Card '!$B$9:$B$710=$C4)*(('P-Card '!$I$9:$I$710="22c")+('P-Card '!$I$9:$I$710="25c")),'P-Card '!$N$9:$N$710,0))
( this formula is entered using the {CTRL + Shift + CR} )
So I graduated to this next formula which was easier in some ways but it's not transportable to a new workbook (as the last one was not either):
=DSUM('NPS Detail'!$A$19:$W$1000,"Exp.",Criteria!$A$2:$P$36)+DSUM('P-Card '!$A$9:$W$710,"Exp.",Criteria!$AA$2:$AO$36)
This one is a simpler but the criteria list gets out of hand and when I transport it to a new workbook it carries all the references to the original workbook with it.
So my idea is to create a function that I believe would copy to another workbook with ease. But I'm having trouble figuring out how to read through the rows of the worksheet and compare particular fields with criteria to determine if I need to add that row to the sum. I also tried to use the DSUM function in my UDF with no success.
Can anyone help point me in the right direction?