G
GRK
I’m 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.
(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.