Keyword Formula (Files Attached)

[Joker3655]

Aloha,

This is Matt our in Honolulu. I'm working with a new resort in Waikiki and have created a Profit and Commission Calculator / Tally Sheet for our Sales Agents and Managers.

Everything is pretty much put in place, however I don't know the formula needed to accomplish what I'd like sheet to do. Thanks for your help.

Attached is a copy of the workbook that contains 3 sheets:

(1/19 - 1/25) Range of Pay Period
(DA COM TALLY) Destiny Agent Commission Tally
(OPC COM TALLY) OPC Commission Tally

Range of Pay Period is where we compute the gross profit and commission due to agents involved in the sale based upon the percentages given.

Destiny Agent Commission Tally and OPC Commission Tally "Read" the information off of the the first sheet and summarize all of the commission due to the agents listed on sheet 1 (Range of Pay Period)

I have highlighted all of the important areas in RED.

I would truly appreciate the assistance of you geniuses in developing a formula that would accomplish the following:

A formula whereby when an agent makes a sale or is eligible for earning commission on page 1, we input the agents "Codename" given on sheet 2 and sheet 3 into the cells on sheet 1 marked in RED.

By doing this, it instructs the appropriate cell in the agent's tally sheet to summarize the amount of commission he's earned from all of the deals that he was a part of on sheet 1.

The ulimate goal is to be able to assign the agents to thier Roles in the sales from sheet 1, and have it tally the full commission total next to thier names and codes on the tally sheet.

I appreciate the attention and expertise to anyone assisting in this matter!

Mahalo,

Matt

[rylo]

Matt

A couple of questions

1) Would the commission for CHRISL be 2851.45 (689.5+439.95+1722)
2) Would the Sales reps always be in the same column or is this just a quirk of the example. So would CHRISL always only appear in column H, or could (s)he appear in any of the commission columns.
3) Same with the OPC - will the reps always only appear in column L.
4) Given your structure, my first call functions may not be easily used (INDEX, MATCH, SUMPRODUCT...). Are you able to have bespoke functions (UDFs) devised to handle your situation. Some places do not allow macros to be used.


rylo

[Joker3655]

Thanks for the reply... Here are the answers to your questions:

A couple of questions

1) Would the commission for CHRISL be 2851.45 (689.5+439.95+1722)

Actually, the commission would be $3,266.45... but you had the right idea, you just left out the last sale ($415) for CHRISL for the Klipstien party.
(689.5+439.95+1722+415)

2) Would the Sales reps always be in the same column or is this just a quirk of the example. So would CHRISL always only appear in column H, or could (s)he appear in any of the commission columns.
CHRISL or any Agent could apear in any of the columns... its just a quirk of the example.

3) Same with the OPC - will the reps always only appear in column L.

Usually, but there are some OPC's that are Sales Agents too, so the formula / macro should be designed so that any rep could appear in any column.

4) Given your structure, my first call functions may not be easily used (INDEX, MATCH, SUMPRODUCT...). Are you able to have bespoke functions (UDFs) devised to handle your situation. Some places do not allow macros to be used.

There are no admin policies that restrict the use of macros. I'm open to any suggestions that you have... Formulas I can pick up on and modify once you share the formula example with me.... Macros, I have no experience with... But I am willing to go with whatever you think is the best method.

-Matt

[rylo]

Matt

Put the code below into a general module in the workbook. In the spreadsheet, go ALT F11, Insert, Module, then paste the code.

Please Login or Register  to view this content.

In sheet DA COM TALLY!C4 enter the formula
=cpc(B4,'1-19 - 1-25'!$H$3:$L$18)

Copy down as required. Same formula in the other sheet.

rylo

[Joker3655]

Rylo... You are the Man!

Whatever magic mojo you just manifested works EXACTLY how I needed it to. Excellent Job!

Now that I have created the module and saved the workbook while it was utilizing the macro, will the macro always be attached to the spreadsheet?

Or will I need to re-input the macro everytime I open the workbook on a different computer?

Also, I am quite intrigued as to how macros work. What can you suggest for me to start learning... square-one begginers guide?

Mahalo,

Matt

[rylo]

Matt

Yes the macro will be attached to the workbook. When you open, you will have to accept the macro option for things to work.

For a start, just record some macros then look at the code it produces. To move on from there, do a search of the site and have a look at responses posted by other posters / responders. Sites such as these are also a good resource as you will get code snips and can pull them apart to see how they work.


rylo