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