Help with Table
Im having difficulty formulating a function that will find an exact cell
(COM1€¦..7) from a table or array.
The table (identified as €śTable1€ť) has 15 columns and 123 rows. The first
column (DAY) represents the number of days 1 through 120 that an invoice is
paid on which needs to be MATCHED from the 2nd sheet, eleventh column
identified as €śNUMBER OF DAYS COLLECTED€ť. The second, fourth, sixth, eight,
tenth, twelfth, and fourteenth columns represents the codes which should be
MATCHED from the third column identified as €śCODE€ť of the 2nd sheet.
If €śNUMBER OF DAYS COLLECTED€ť is 48 and €śCODE€ť is 1 the answer is 0.0, how
do I write this function that selects the correct commission (COM1) result?
TABLE1 (1ST SHEET)
DAY CODE1 COM1 CODE2 COM2 CODE3 COM3 €¦..CODE7 COM7
1 1 50.0 2 50.0 3 50.0 7
50.0
2 1 30.0 2 50.0 3 30.0 7
50.0
3 1 30.0 2 30.0 3 30.0 7
50.0
4 1 30.0 2 10.0 3 30.0 7 30.0
5 1 10.0 2 10.0 3 10.0 7
30.0
25 1 0.0 2 10.0 3 10.0 7 30.0
75 1 0.0 2 0.0 3 0.0 7
10.0
120 1 0.0 2 0.0 3 0.0 7
0.0
2ND SHEET
DATE DATE CODE CUSTOMER INVOICE INVOICE RESID
TOTAL
NUMBER DESCRIPTION NUMBER AMOUNT SALES
RESID
3/26/2008 39533 1 RESID COD AXCZ 1200.00 1200.00
1200.00
RESID FIN
29000.00 29000.00 30200.00
DATE DATE NUMBER COM TOTAL
COLLECTED NUMBER OF DAYS % COMM
COLLECTED PAID
5/13/2008 39581 48
Any help is greatly appreciated. The version of the product is Office
Professional 2007.
|