Lookup Table help

  • Thread starter Thread starter KaiWalk
  • Start date Start date
K

KaiWalk

Hi all, new member. Be gentle.

I'm creating a worksheet that will be used to list individua
transactions (300-400) daily. It will have few merchants (15-20) wit
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 thei
transactions that receives a percentage of their fee.

How do I create a table that basically says "if merchant is XYZ, ente
salesman name in B15, enter that salesman's commission formula in B1
(the formula would be a specified percent of the fee)"

My ultimate goal is to also have per merchant & per salesman summar
pages that have SUMIF statements that determine the final amount due t
each merchant & each salesman, possile combined with mail-merge info a
well.

I guestimate that I am an intermediate Excel user.

Thx for the help
 
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(Merchants!$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(Transactions!$A:$A)-1,1)
TransactMerchant=OFFSET(Transactions!$C$1,1,,COUNTA(Transactions!$A:$A)-1,1)
TransactAmount=OFFSET(Transactions!$D$1,1,,COUNTA(Transactions!$A:$A)-1,1)
TransactMerchantFee=OFFSET(Transactions!$I$1,1,,COUNTA(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.
 
Back
Top