Help with Table

[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.

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[GRK]

Pete,

Thank you for the information. I will follow your advice and let you know
the results tomorrow.

GRK

 

[GRK]

Pete,

The solution did not work, is there a way for me to email the excel file to
you?

 

[Pete_UK]

You can send it to:

pashurst <at> auditel.net

Change the obvious.

Pete

 

[Dave Peterson]

Pete,

You may want to munge your email address in Google, too.

It shows up (correctly) in the headers of all your posts.