E
eec
A B C D E F G H I
Acct# Inv# Date Amt Sales Total Alloc Alloc Alloc
Tax Amt
Amt S.Tax Total
68847 121508 1/15/09 $2,358 $2,358 $0 $0 $0
68847 011509 2/17/09 $1,470 $30 $1,500 $0 $0 $0
75181 110509 12/7/09 $890 $10 $900 $0 $0 $0
9201(II) 1209/75 5/12/09 $1,200 $0 $1,200 $672 $0 $672
9201(III) 1209/75 5/12/09 $528 $0 $528 $0 $0 $0
Invoice data is on Sheet2, on Sheet1 I need to pull information based on
invoice # to submit with the payment of the invoice. I need to allocate some
invoices based on invoice number as we receive 1 invoice but it is paid by
different divisions.
The following array formula works pulling most data I need except the amount
because I need it to pull from column I if it has data and column F if column
I is = 0.
Sheet1 cell A1 is the invoice number I want to pull data for. Sheet1 cell
A2 is a count if function to total occurances of that invoice #.
=IF(ROWS(A$3:A3)>$A$2,â€
“,Index(Sheet2!F:F,SMALL(IF(Sheet2!B:B=$A$1,ROW(Sheet2!F:F)),ROW(1:1))))
I need to manipulate this formula or come up with a whole new one that pulls
column I if there is data there and column F if column I = 0.
Acct# Inv# Date Amt Sales Total Alloc Alloc Alloc
Tax Amt
Amt S.Tax Total
68847 121508 1/15/09 $2,358 $2,358 $0 $0 $0
68847 011509 2/17/09 $1,470 $30 $1,500 $0 $0 $0
75181 110509 12/7/09 $890 $10 $900 $0 $0 $0
9201(II) 1209/75 5/12/09 $1,200 $0 $1,200 $672 $0 $672
9201(III) 1209/75 5/12/09 $528 $0 $528 $0 $0 $0
Invoice data is on Sheet2, on Sheet1 I need to pull information based on
invoice # to submit with the payment of the invoice. I need to allocate some
invoices based on invoice number as we receive 1 invoice but it is paid by
different divisions.
The following array formula works pulling most data I need except the amount
because I need it to pull from column I if it has data and column F if column
I is = 0.
Sheet1 cell A1 is the invoice number I want to pull data for. Sheet1 cell
A2 is a count if function to total occurances of that invoice #.
=IF(ROWS(A$3:A3)>$A$2,â€
“,Index(Sheet2!F:F,SMALL(IF(Sheet2!B:B=$A$1,ROW(Sheet2!F:F)),ROW(1:1))))
I need to manipulate this formula or come up with a whole new one that pulls
column I if there is data there and column F if column I = 0.