Lookup Table help

[KaiWalk]

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.

[Arvi Laanemets]

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.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"KaiWalk" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>