If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can Excel Rtrn a Value by Referencing Multiple Rows and Columns
I have a reference table which contains multiple commission %'s in 21 columns
labeled with 3 letter codes; with rows of commission sources in column A. Each commission source in column A has multiple %'s payable based on the three letter code or type. I need Excel to look at a row on a daily data sheet which contains a commission source in C and a three letter % code in F, then go to the table, find the intersection of those two references and return the valid % to the current data sheet. The ultimate purpose is to recalulate the commission %'s received (daily data sheet) against contracted %'s (reference table) and find variances based on commission source and type. |
#2
|
|||
|
|||
Can Excel Rtrn a Value by Referencing Multiple Rows and Columns
Hi,
It would be nice to see a sample data layout. However, if you range name the columns containing the 3 letter code with those 3 letter names and the rows with the commision source names and you enter the source in C1 and the 3 letter code in F1 then =INDIRECT(C1) INDIRECT(F1) will return the % at the intersection of the two references. To name all the ranges select your table including the 3 letter codes at the top and the commission source column on the left and choose Insert, Name, Create. Note the above formula is in one cell and has a space between the two INDIRECT functions, the space is Excel's intersect operator. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kim" wrote: I have a reference table which contains multiple commission %'s in 21 columns labeled with 3 letter codes; with rows of commission sources in column A. Each commission source in column A has multiple %'s payable based on the three letter code or type. I need Excel to look at a row on a daily data sheet which contains a commission source in C and a three letter % code in F, then go to the table, find the intersection of those two references and return the valid % to the current data sheet. The ultimate purpose is to recalulate the commission %'s received (daily data sheet) against contracted %'s (reference table) and find variances based on commission source and type. |
#3
|
|||
|
|||
Can Excel Rtrn a Value by Referencing Multiple Rows and Column
Thanks Shane,
Below are samples of the worksheets involved. I need a formula in the "Contract %" column on the "Daily Data" sheet which will return a value from the "Source Table" for the "Source" and "Type" listed in the row on the daily data sheet. There are 50 "Sources" and 21 columns of possible "Types". Daily Data Date Source Type Gross Com Com% Contract % 6/1/09 California EXE 10.00 2.80 28.00% 6/1/09 Florida SPR 14.00 3.92 28.00% 6/1/09 Michigan TRF 2.00 0.56 28.00% 6/1/09 California SPR 11.00 3.08 28.00% 6/1/09 Florida EXE 2.00 0.56 28.00% 6/1/09 Michigan EXE 11.00 3.08 28.00% 6/1/09 New York SPR 2.00 0.56 28.00% Contract Commissions Table Com SourceEXE SPR TRF California 18.00% 21.00% 21.00% Florida 17.00% 20.50% 20.50% Michigan 17.00% 20.50% 20.50% New York 18.00% 24.00% 24.00% I would be so grateful if you can help. The daily data comes in with 8000 lines per week and it would be a nightmare to hand-key those contract %'s for comparison. Cheers to you as well, Kim "Shane Devenshire" wrote: Hi, It would be nice to see a sample data layout. However, if you range name the columns containing the 3 letter code with those 3 letter names and the rows with the commision source names and you enter the source in C1 and the 3 letter code in F1 then =INDIRECT(C1) INDIRECT(F1) will return the % at the intersection of the two references. To name all the ranges select your table including the 3 letter codes at the top and the commission source column on the left and choose Insert, Name, Create. Note the above formula is in one cell and has a space between the two INDIRECT functions, the space is Excel's intersect operator. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kim" wrote: I have a reference table which contains multiple commission %'s in 21 columns labeled with 3 letter codes; with rows of commission sources in column A. Each commission source in column A has multiple %'s payable based on the three letter code or type. I need Excel to look at a row on a daily data sheet which contains a commission source in C and a three letter % code in F, then go to the table, find the intersection of those two references and return the valid % to the current data sheet. The ultimate purpose is to recalulate the commission %'s received (daily data sheet) against contracted %'s (reference table) and find variances based on commission source and type. |
Thread Tools | |
Display Modes | |
|
|