|
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. |
Help with Table
I'm not sure why you need the columns CODE1, CODE2, CODE3 etc in your table
on Sheet1 - can these be removed? Also, I'm not sure how many rows your headings take up on Sheet2 - what is the exact cell reference of the "Number of Days Collected" on this sheet? Is it a single cell, or a column? Pete "GRK" wrote in message ... 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. |
Help with Table
I'm going to bed soon, so I'll post my solution and assumptions so
that you can try it out. First of all, if your table on Sheet1 starts with DAY in A1 and the other headings are on row 1, then your data will cover A2:O121. Although the CODE1, CODE2 columns are not needed, I've included them in my setup to mimic your situation. On Sheet2 I put all the headings on row 1 and used Wrap Text to get them to cover 3 lines. Thus my data is in row 2, with CODE in C2 and NUMBER OF DAYS COLLECTED in K2. So in L2 I put this formula: =INDEX(Sheet1!A$2:O$121,MATCH(K2,Sheet1!A$2:A$121, 0),2*C2+1) This returns the appropriate value from your table, but if you want it to be treated as a percentage then you should divide by 100 and use Format | Cells to get it into the correct format. If your data layout is different to mine, then hopefully you can see how and where to change the cell references in the formula. Hope this helps. Pete On Apr 12, 12:00*am, "Pete_UK" wrote: I'm not sure why you need the columns CODE1, CODE2, CODE3 etc in your table on Sheet1 - can these be removed? Also, I'm not sure how many rows your headings take up on Sheet2 - what is the exact cell reference of the "Number of Days Collected" on this sheet? Is it a single cell, or a column? Pete "GRK" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Help with Table
Pete,
Thank you for the information. I will follow your advice and let you know the results tomorrow. GRK -- GRK "Pete_UK" wrote: I'm going to bed soon, so I'll post my solution and assumptions so that you can try it out. First of all, if your table on Sheet1 starts with DAY in A1 and the other headings are on row 1, then your data will cover A2:O121. Although the CODE1, CODE2 columns are not needed, I've included them in my setup to mimic your situation. On Sheet2 I put all the headings on row 1 and used Wrap Text to get them to cover 3 lines. Thus my data is in row 2, with CODE in C2 and NUMBER OF DAYS COLLECTED in K2. So in L2 I put this formula: =INDEX(Sheet1!A$2:O$121,MATCH(K2,Sheet1!A$2:A$121, 0),2*C2+1) This returns the appropriate value from your table, but if you want it to be treated as a percentage then you should divide by 100 and use Format | Cells to get it into the correct format. If your data layout is different to mine, then hopefully you can see how and where to change the cell references in the formula. Hope this helps. Pete On Apr 12, 12:00 am, "Pete_UK" wrote: I'm not sure why you need the columns CODE1, CODE2, CODE3 etc in your table on Sheet1 - can these be removed? Also, I'm not sure how many rows your headings take up on Sheet2 - what is the exact cell reference of the "Number of Days Collected" on this sheet? Is it a single cell, or a column? Pete "GRK" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Help with Table
Pete,
The solution did not work, is there a way for me to email the excel file to you? -- GRK "Pete_UK" wrote: I'm going to bed soon, so I'll post my solution and assumptions so that you can try it out. First of all, if your table on Sheet1 starts with DAY in A1 and the other headings are on row 1, then your data will cover A2:O121. Although the CODE1, CODE2 columns are not needed, I've included them in my setup to mimic your situation. On Sheet2 I put all the headings on row 1 and used Wrap Text to get them to cover 3 lines. Thus my data is in row 2, with CODE in C2 and NUMBER OF DAYS COLLECTED in K2. So in L2 I put this formula: =INDEX(Sheet1!A$2:O$121,MATCH(K2,Sheet1!A$2:A$121, 0),2*C2+1) This returns the appropriate value from your table, but if you want it to be treated as a percentage then you should divide by 100 and use Format | Cells to get it into the correct format. If your data layout is different to mine, then hopefully you can see how and where to change the cell references in the formula. Hope this helps. Pete On Apr 12, 12:00 am, "Pete_UK" wrote: I'm not sure why you need the columns CODE1, CODE2, CODE3 etc in your table on Sheet1 - can these be removed? Also, I'm not sure how many rows your headings take up on Sheet2 - what is the exact cell reference of the "Number of Days Collected" on this sheet? Is it a single cell, or a column? Pete "GRK" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
Help with Table
You can send it to:
pashurst <at auditel.net Change the obvious. Pete On Apr 12, 2:54*am, GRK wrote: Pete, The solution did not work, is there a way for me to email the excel file to you? -- GRK |
Help with Table
Pete,
You may want to munge your email address in Google, too. It shows up (correctly) in the headers of all your posts. Pete_UK wrote: You can send it to: pashurst <at auditel.net Change the obvious. Pete On Apr 12, 2:54 am, GRK wrote: Pete, The solution did not work, is there a way for me to email the excel file to you? -- GRK -- Dave Peterson |
| All times are GMT +1. The time now is 12:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com