Prev: Can offset be used in this formula?
Next: Printing a color document - color is fading
From: eec on 8 Mar 2010 09:57
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.

 | 
Pages: 1
Prev: Can offset be used in this formula?
Next: Printing a color document - color is fading