+ Reply to Thread
Results 1 to 8 of 8

Looking to create a dynamic commissions sheet based on agent and product

  1. #1
    Registered User
    Join Date
    01-03-2019
    Location
    Nassau, Bahamas
    MS-Off Ver
    365
    Posts
    7

    Looking to create a dynamic commissions sheet based on agent and product

    Good day,

    Hoping someone can assist.

    Backstory:
    Some agents make a bit more than others since they're on a new contract. Different ranges of products give different amounts of commissions.

    Example:
    Lucy sells Product A. She gets $5 commission from it.
    Sally sells Product A. She gets $10 commission from it since she's on the new contract.
    Lucy sells Product K. She gets $10 commission from it since it's in the next tier of products.
    Sally sells Product K. She gets $15 commission from it, again, new contract but on the next tier of products.

    I don't mind keeping the list of products and agents for it to check against on another sheet.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Looking to create a dynamic commissions sheet based on agent and product

    Please upload a representative Excel workbook. The your source data layout is to be considered in formula solutions.

    (Of note: The 'paperclip' icon has not worked for some time. So please save yourself some grief.)
    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    01-03-2019
    Location
    Nassau, Bahamas
    MS-Off Ver
    365
    Posts
    7

    Re: Looking to create a dynamic commissions sheet based on agent and product

    Sorry for the long wait time on this. Please see the attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-24-2011
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Looking to create a dynamic commissions sheet based on agent and product

    Here is one solution. I created Tiers based on the device, and then assigned values to the commission based on the tier. On the Agent Sales sheet I added two columns (F&G) and changed the summing in the Total Commission column.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-03-2019
    Location
    Nassau, Bahamas
    MS-Off Ver
    365
    Posts
    7

    Re: Looking to create a dynamic commissions sheet based on agent and product

    This works great!

    Though now I need to add in some extra things. So, based on what you have setup, is it possible to then check against some other products - the thing is these don't have tiers unlike the previous ones. Was wondering if it was placed as a different tier, say Tier 0, it would check to see what the amounts are for that product, times it by 1 (which is in the top right side with the other tiered pricing)?

    Probably easier to see what I mean by looking at the document.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,466

    Re: Looking to create a dynamic commissions sheet based on agent and product

    Perhaps the following will help.
    1. On the Device Range sheet, I placed the amount of the commission in column C
    2. In H:K I placed a multiplier, i.e. persons 13 and 14 get twice the commission of their counterparts for tier one sales and half again as much on tier two sales.
    3. on the Agent Sales sheet column F uses: =INDEX('Device Range'!$B$2:$B$65,MATCH([@[Product Name]],'Device Range'!$A$2:$A$65,0))
    4. Column G uses:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    01-03-2019
    Location
    Nassau, Bahamas
    MS-Off Ver
    365
    Posts
    7

    Re: Looking to create a dynamic commissions sheet based on agent and product

    I'm honestly not sure why I didn't think to put it like that, but holy crap this makes more sense than the way I was going about it.

    Thank you!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,466

    Re: Looking to create a dynamic commissions sheet based on agent and product

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-06-2018, 11:52 AM
  2. Create a dynamic comment based on values in another sheet
    By kinscy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-16-2016, 08:50 PM
  3. Create a dynamic list based on multiple dynamic factors.
    By sabin348 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-07-2015, 11:05 AM
  4. Replies: 9
    Last Post: 10-16-2014, 07:46 AM
  5. Want to Create a Dynamic List Excel based on a few columns in another sheet .
    By debsankardas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2013, 10:58 AM
  6. [SOLVED] Find Product Details in Another Sheet based on Product ID and Copy some Fields From there
    By kevalkothari in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2012, 10:43 AM
  7. Replies: 3
    Last Post: 12-23-2009, 07:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1