Lookup Table help
Hi all, new member. Be gentle. I'm creating a worksheet that will be used to list individual transactions (300-400) daily. It will have few merchants (15-20) with numerous individual transactions and changing fees ($15-$50). The merchants will be entered via a dropdown menu I create. Certain merchants will have a salesman associated with their transactions that receives a percentage of their fee. How do I create a table that basically says "if merchant is XYZ, enter salesman name in B15, enter that salesman's commission formula in B16 (the formula would be a specified percent of the fee)" My ultimate goal is to also have per merchant & per salesman summary pages that have SUMIF statements that determine the final amount due to each merchant & each salesman, possile combined with mail-merge info as well. I guestimate that I am an intermediate Excel user. Thx for the help. -- KaiWalk ------------------------------------------------------------------------ KaiWalk's Profile: http://www.excelforum.com/member.php...o&userid=27815 View this thread: http://www.excelforum.com/showthread...hreadid=473256 |
Hi
Create a sheet Merchants, with table (headers in row 1) Merchant, Salesman, Salesman% (the salesman percent from merchant fee, entered as #%) Create dynamic named ranges Merchant=OFFSET(Merchants!$A$1,1,,COUNTA(Merchants !$A:$A)-1,1) MerchantsTbl=OFFSET(Merchants!$A$1,1,,COUNTA(Merch ants!$A:$A)-1,3) I don't know your Transactions sheet real layout, so let it be Date, Transact, Merchant, Amount, Fee%, TotalFee, Salesman, SalesmanFee, MerchantFee Format the cell A2 (Date) in any valid date format For cell C2 (column Merchant), you apply (applied?) data validation list with source =Merchant Into column Fee% you will enter the total fee percent in format # (when you use the format #%, then you have to modify the formula in column TotalFee). Into remaining columns, you enter into row 2 formulas: F2=IF(OR(D2="",E2=""),"",D2*E2/100) (TotalFee) G2=IF(OR(D2="",ISERROR(VLOOKUP(C2,MerchantsTbl,2,0 ))),"",VLOOKUP(C2,MerchantsTbl,2,0)) (Salesman) H2=IF(OR(G2="",F2=""),"",VLOOKUP(C2,MerchantTbl,3, 0)*F2) (SalesmanFee) I2=IF(F2="","",SUM(F2,-H2)) (MerchantFee) Copy the row 2 down for amount of rows, reasonable for your table. Create pages MerchantSummary, SalesmanSummary. There are various ways to get such tables. Here one example (on fly) for sheet MerchantSummary A1="Date" B1 - you enter the date here (format the cell accordingly) A3="Merchant", B3="Amount", C3="Fee" A4=IF(ISERROR(INDEX(Merchant,ROW()-3,1)),"",INDEX(Merchant,ROW()-3,1)) Create named ranges TransactDate=OFFSET(Transactions!$A$1,1,,COUNTA(Tr ansactions!$A:$A)-1,1) TransactMerchant=OFFSET(Transactions!$C$1,1,,COUNT A(Transactions!$A:$A)-1,1) TransactAmount=OFFSET(Transactions!$D$1,1,,COUNTA( Transactions!$A:$A)-1,1) TransactMerchantFee=OFFSET(Transactions!$I$1,1,,CO UNTA(Transactions!$A:$A)-1,1) B4=IF(A4="","",SUMPRODUCT(--(TransactDate=$B$1),--(TransactMerchant=A4),TransactAmount)) C4=IF(A4="","",SUMPRODUCT(--(TransactDate=$B$1),--(TransactMerchant=A4),TransactMerchantFee)) Copy row 4 down for some reasonable amount of rows. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "KaiWalk" wrote in message ... Hi all, new member. Be gentle. I'm creating a worksheet that will be used to list individual transactions (300-400) daily. It will have few merchants (15-20) with numerous individual transactions and changing fees ($15-$50). The merchants will be entered via a dropdown menu I create. Certain merchants will have a salesman associated with their transactions that receives a percentage of their fee. How do I create a table that basically says "if merchant is XYZ, enter salesman name in B15, enter that salesman's commission formula in B16 (the formula would be a specified percent of the fee)" My ultimate goal is to also have per merchant & per salesman summary pages that have SUMIF statements that determine the final amount due to each merchant & each salesman, possile combined with mail-merge info as well. I guestimate that I am an intermediate Excel user. Thx for the help. -- KaiWalk ------------------------------------------------------------------------ KaiWalk's Profile: http://www.excelforum.com/member.php...o&userid=27815 View this thread: http://www.excelforum.com/showthread...hreadid=473256 |
All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com