mdg1
asked on
Part II of Excel lookup of data and copy rows to new worksheet
This is the second part to the original question - https://www.experts-exchange.com/questions/27287678/Excel-lookup-of-data-and-copy-rows-to-new-worksheet.html?cid=1575&anchorAnswerId=36500735#a36500735
Now that we have created the separate worksheets for each sales person, I'd like to have a summary of the totals owed to each sales person. As they are paid commission I need to manually indicate the amount paid but want to make sure that this info is not overwritten when the amounts owed are updated each time.
Now that we have created the separate worksheets for each sales person, I'd like to have a summary of the totals owed to each sales person. As they are paid commission I need to manually indicate the amount paid but want to make sure that this info is not overwritten when the amounts owed are updated each time.
ASKER
Sure - I added a few columns that I realized that I needed on the main page and then added the worksheet call "Commission SUmmary Report."
So - IDEALLY...
1. A calculation can be automatically performed for the commission amount on the main worksheet. You will see that sometimes there is a flat dollar commission instead of a %. I'm thinking there may need to be a if/then stmt that indicates that if it's not a percentage then to take the flat dollar amt???
2. The added worksheet - commission summary report - will automatically update "potential" and "earned" commission but not overwrite the Paid amount that will be added manually.
Let me know if you need more explanation.
This is great!!!!
Salesman-Split-comm-summ.xlsm
So - IDEALLY...
1. A calculation can be automatically performed for the commission amount on the main worksheet. You will see that sometimes there is a flat dollar commission instead of a %. I'm thinking there may need to be a if/then stmt that indicates that if it's not a percentage then to take the flat dollar amt???
2. The added worksheet - commission summary report - will automatically update "potential" and "earned" commission but not overwrite the Paid amount that will be added manually.
Let me know if you need more explanation.
This is great!!!!
Salesman-Split-comm-summ.xlsm
What about Potential Commission - SP #2 Earned Commission - SP #2
how are they taken into concideration in your summary commission you seemed to have given an example showing only
Potential Commission - SP #1 Earned Commission - SP #1
whereas for Sales-JG (which happens to be coincidently my initials !!!) you have data for all of
Potential Commission - SP #1 Earned Commission - SP #1 Potential Commission - SP #2 Earned Commission - SP #2
So question:
1) how do you account for htese diffrent SP1 SP2 Potential / Earned (shall we add them together and show them under Potential and Earned in the sheet ?
2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run !
3) What about when you make modifications/addition/del
4) In your sheet that you created there is no date or month is this normal ?
5) You have a column Paid will you update that manually there ?
gowflow
how are they taken into concideration in your summary commission you seemed to have given an example showing only
Potential Commission - SP #1 Earned Commission - SP #1
whereas for Sales-JG (which happens to be coincidently my initials !!!) you have data for all of
Potential Commission - SP #1 Earned Commission - SP #1 Potential Commission - SP #2 Earned Commission - SP #2
So question:
1) how do you account for htese diffrent SP1 SP2 Potential / Earned (shall we add them together and show them under Potential and Earned in the sheet ?
2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run !
3) What about when you make modifications/addition/del
4) In your sheet that you created there is no date or month is this normal ?
5) You have a column Paid will you update that manually there ?
gowflow
ASKER
1) how do you account for htese diffrent SP1 SP2 Potential / Earned (shall we add them together and show them under Potential and Earned in the sheet ? - I GUESS THIS GETS CONFUSING...I THINK HERE, I SHOULD HAVE CALLED JG SP#1 SINCE THIS SALES PERSON IS REOCCURING AND THE OTHERS AS SP#2. I DID CALCULATE THOSE POTENTIAL AND EARNED COMMISSIONS. NOT SURE IF I AM ANSWERING YOUR QUESTION HERE. I DO NEED THESE SEPARATED AS IT WILLL BE FOR TWO DIFFERENT PEOPLE WHO ARE SHARING THE COMM.
2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run ! NO SET FREQUENCY TO RUN THE COMPUTATION. FROM TIME TO TIME WE EITHER REPORT ON WHAT THE SALESPERSON HAS EARNED OR WE PAYOUT ON EARNED COMMISSIONS. IDEALLY, I'D LIKE ALL NEWER ADDITIONS ON THE BOTTOM
3) What about when you make modifications/addition/del
4) In your sheet that you created there is no date or month is this normal ? THERE WILL BE DATES - I JUST SIMPLIFIED IT FOR OUR EXAMPLE
5) You have a column Paid will you update that manually there ? YES - WILL MANUALLY UPDATE THE PAID COLUMN
THANKS, JG!
2) How often you want to run this commission computation ? Should it add on top what is new ? if yues then we will need to flag all items that were transfered to commission so we don't take them into account on the next run ! NO SET FREQUENCY TO RUN THE COMPUTATION. FROM TIME TO TIME WE EITHER REPORT ON WHAT THE SALESPERSON HAS EARNED OR WE PAYOUT ON EARNED COMMISSIONS. IDEALLY, I'D LIKE ALL NEWER ADDITIONS ON THE BOTTOM
3) What about when you make modifications/addition/del
4) In your sheet that you created there is no date or month is this normal ? THERE WILL BE DATES - I JUST SIMPLIFIED IT FOR OUR EXAMPLE
5) You have a column Paid will you update that manually there ? YES - WILL MANUALLY UPDATE THE PAID COLUMN
THANKS, JG!
Sorry hv been caught with other urgent issues will give it a look once possible tks for your patience.
gowflow
gowflow
Sorry again for this delay your the next inline I should hv something for you later today !! Tks ur patience. Sorry again
gowflow
gowflow
I am reading again your last post now that I need to developp something and seems you are not sure what you want !! in item 1) you seem to be hasty and not sure if you add or not the SP's in item 2 you mention that all new items will go to the bottom each time so you will have something like this
in the sheet Commission Summary Report
Salesperson Potential Earned Paid Owe
AB 250.00 125.00 125.00
CD 500.00 500.00 500.00
EF
GH
...
then
Salesperson Potential Earned Paid Owe
AB 850.00 225.00 675.00
CD 800.00 700.00 100.00
EF 350.00 450.00 100.00
etc,,,
Question how can you trace 1 salesman situation if new items comes at the bottom each time you run them ??? Then also in 3) your not clear on the answer then on 4) you simplified by not putting dates !!!!!
HEY HOOOO !!!!! I need something clear with dates and exactly on what you need so I can build a scheme or else sorry you need to get your ideas together and when it is clear let me know.
gowflow
in the sheet Commission Summary Report
Salesperson Potential Earned Paid Owe
AB 250.00 125.00 125.00
CD 500.00 500.00 500.00
EF
GH
...
then
Salesperson Potential Earned Paid Owe
AB 850.00 225.00 675.00
CD 800.00 700.00 100.00
EF 350.00 450.00 100.00
etc,,,
Question how can you trace 1 salesman situation if new items comes at the bottom each time you run them ??? Then also in 3) your not clear on the answer then on 4) you simplified by not putting dates !!!!!
HEY HOOOO !!!!! I need something clear with dates and exactly on what you need so I can build a scheme or else sorry you need to get your ideas together and when it is clear let me know.
gowflow
mdg1
Here is my suggestion and solution for you to the best of my knowledge and with the limited info oyu provided and the multiple tweaky options you had as per my previous post.
Content
1) The formulas for Commissions in
Col O titles Potential Commission - SP #1
Col P Earned Commission - SP #1
Col Q Potential Commission - SP #2
Col R Earned Commission - SP #2
Have all been ammended so if there is a percentage it will take the formulas you already had (perc * amount) if no percent but flat amount then it will take the flat amount.
2) Each and every time you run the previous Macro: Split Worksheet by Salesman then all the data that exist in the sheet 'Commission Summary Report' will be automatically deleted. becoz when you run this macro it automatically delete all sales sheets and redo them from scratch then it is logical not to keep trace of any commission that was previously transfered to this sheet so that data is always consistent.
3) I have created a new button called: Compile Commissions what will it do is the follwoing:
It will go thru all the SALES- sheets and look each and every record and if the last column (AQ Moved to Commission column that I created) is blank then it will take this data in concideration and after moving it to commission Summary it will flag it to YES so that if you run this commission report the next time it will not take this data anymore hence avoiding duplicates. It will do this for all the sheets and you will get a compilation in this Commission Summary Sheet.
Please run the file try first to run the Spilt Worksheets by Salesman and go check the results. then run Compile comission and check the results.
Pls let me know your fedback so we can adjust.
gowflow
Salesman-Split-comm-110912.xlsm
Here is my suggestion and solution for you to the best of my knowledge and with the limited info oyu provided and the multiple tweaky options you had as per my previous post.
Content
1) The formulas for Commissions in
Col O titles Potential Commission - SP #1
Col P Earned Commission - SP #1
Col Q Potential Commission - SP #2
Col R Earned Commission - SP #2
Have all been ammended so if there is a percentage it will take the formulas you already had (perc * amount) if no percent but flat amount then it will take the flat amount.
2) Each and every time you run the previous Macro: Split Worksheet by Salesman then all the data that exist in the sheet 'Commission Summary Report' will be automatically deleted. becoz when you run this macro it automatically delete all sales sheets and redo them from scratch then it is logical not to keep trace of any commission that was previously transfered to this sheet so that data is always consistent.
3) I have created a new button called: Compile Commissions what will it do is the follwoing:
It will go thru all the SALES- sheets and look each and every record and if the last column (AQ Moved to Commission column that I created) is blank then it will take this data in concideration and after moving it to commission Summary it will flag it to YES so that if you run this commission report the next time it will not take this data anymore hence avoiding duplicates. It will do this for all the sheets and you will get a compilation in this Commission Summary Sheet.
Please run the file try first to run the Spilt Worksheets by Salesman and go check the results. then run Compile comission and check the results.
Pls let me know your fedback so we can adjust.
gowflow
Salesman-Split-comm-110912.xlsm
ASKER
Hi There,
I'm sorry I haven't been so clear. With each new aspect that you present me with, I realize that I need to re-think our needs. Having said that, what you've done so far is remarkable. Here are some things that I don't understand fully or are a little problematic...
It looks like if I list two salespeople in column L and if say someone who had always been listed second - say TT - is now listed first - then there is an entire new spreadsheet created for her as opposed to be included in tab for which she is listed second. Can this be fixed?
Second - I may likely be misunderstanding your instructions/explanation about the commissions summary...If I indicate that something was paid - Column J - this of course gets wiped out when I run the commissions macro again. So, I'm unclear as to how I keep track of what has been paid out and therefore owing...
Please lose patience with me... we are almost there and I truly apprciate this remarkable system you are creating for me!
Thanks.
I'm sorry I haven't been so clear. With each new aspect that you present me with, I realize that I need to re-think our needs. Having said that, what you've done so far is remarkable. Here are some things that I don't understand fully or are a little problematic...
It looks like if I list two salespeople in column L and if say someone who had always been listed second - say TT - is now listed first - then there is an entire new spreadsheet created for her as opposed to be included in tab for which she is listed second. Can this be fixed?
Second - I may likely be misunderstanding your instructions/explanation about the commissions summary...If I indicate that something was paid - Column J - this of course gets wiped out when I run the commissions macro again. So, I'm unclear as to how I keep track of what has been paid out and therefore owing...
Please lose patience with me... we are almost there and I truly apprciate this remarkable system you are creating for me!
Thanks.
ok here it is:
Let me first address your issues:
1)
It looks like if I list two salespeople in column L and if say someone who had always been listed second ....
>>> Well the issue is not as you discribed list first or second does not matter but what happened in fact is when you switched the salesman you added a space inadvertantly before the salesman name which resulted in a new sheet creation " TT" where before it was "TT". Anyway this has been addressed in this version and wether small letters/capital/spaces it will always find the correct salesman and affect the data to the existing sheet.
2)
Second - I may likely be misunderstanding your instructions/explanation about the commissions
>>> well I am trying to create a model for you based on the so multiple if and but and open possibilities whcih obviously the past file was not optimum and had still issues to be set and dealt with.
ABOUT THIS VERSION
===================
This version is somewhat closer to a package but this means flexibility is somewhat more restrictive. In principle the more flexibility to do whatever you want with data any time the more developpement time to adapt to all possible situations.
In this version you will see following Buttons
1) Delete Sales WS and Rest Commission in red
This button is made when you need to rest your workbook it will delete all sales worksheets, rest col that keep track that sales has been distributed and rest all commission data. This is good when you are starting a year fresh or a period (3 months or 6 months ... whatever ) and you still have data in the main sheet and you need to start from scratch.
2) Split Worksheet by Salesman: I have now created a new Col AR 'Moved to Sales' that will flag every record that is moved to a Sales worksheet. So when you run this procedure next time it will not move the record again. So basically every day when you enter data in Main Summary Page (as long as there is no Salesman code) the record will never be moved. If you are done with the record then you label its salesman and when you run this routine it will move it to the coresponding sheet.
3) Compile Commissions
This button will go thru all the Sales sheets and every record where Col AQ moved to commission is blank will calucualte the data and affect it in Summary Commission and will label Col AQ with YES so it is not duplicated on next run.
LIMITATIONS
===========
You have to understand that when a record is flaged with YES wether in Main Summary Sheet or in individual Sales Sheets then any modification to the actual record will not be populated in the related sheets. Like if the record in Main Summary Sheet has already been distributed to sales sheet if you decide that you want to revise the rate or the commission then as the record is labeled YES all changes will not be recorded. Now if you remove the YES manually deleted it from Main Summary sheet then you need to go to the coresponding sales sheet and manually delete the coresponding record and if the commission was captured also go in the commission sheet and subtract the said amount (I am outlining the limitation and do not favor manual correction but drawing your attention for maybe rasing new issues that we can probably solve by automation in new questions)
Hope I have help you to see clearer in this pls do not hesitate to let me kow of your comments and suggestions. Finally as long as you don't activate the Delete worksheets then you will always have your data and whatever you input in commissions.
gowflow
Salesman-Split-comm-110913.xlsm
Let me first address your issues:
1)
It looks like if I list two salespeople in column L and if say someone who had always been listed second ....
>>> Well the issue is not as you discribed list first or second does not matter but what happened in fact is when you switched the salesman you added a space inadvertantly before the salesman name which resulted in a new sheet creation " TT" where before it was "TT". Anyway this has been addressed in this version and wether small letters/capital/spaces it will always find the correct salesman and affect the data to the existing sheet.
2)
Second - I may likely be misunderstanding your instructions/explanation about the commissions
>>> well I am trying to create a model for you based on the so multiple if and but and open possibilities whcih obviously the past file was not optimum and had still issues to be set and dealt with.
ABOUT THIS VERSION
===================
This version is somewhat closer to a package but this means flexibility is somewhat more restrictive. In principle the more flexibility to do whatever you want with data any time the more developpement time to adapt to all possible situations.
In this version you will see following Buttons
1) Delete Sales WS and Rest Commission in red
This button is made when you need to rest your workbook it will delete all sales worksheets, rest col that keep track that sales has been distributed and rest all commission data. This is good when you are starting a year fresh or a period (3 months or 6 months ... whatever ) and you still have data in the main sheet and you need to start from scratch.
2) Split Worksheet by Salesman: I have now created a new Col AR 'Moved to Sales' that will flag every record that is moved to a Sales worksheet. So when you run this procedure next time it will not move the record again. So basically every day when you enter data in Main Summary Page (as long as there is no Salesman code) the record will never be moved. If you are done with the record then you label its salesman and when you run this routine it will move it to the coresponding sheet.
3) Compile Commissions
This button will go thru all the Sales sheets and every record where Col AQ moved to commission is blank will calucualte the data and affect it in Summary Commission and will label Col AQ with YES so it is not duplicated on next run.
LIMITATIONS
===========
You have to understand that when a record is flaged with YES wether in Main Summary Sheet or in individual Sales Sheets then any modification to the actual record will not be populated in the related sheets. Like if the record in Main Summary Sheet has already been distributed to sales sheet if you decide that you want to revise the rate or the commission then as the record is labeled YES all changes will not be recorded. Now if you remove the YES manually deleted it from Main Summary sheet then you need to go to the coresponding sales sheet and manually delete the coresponding record and if the commission was captured also go in the commission sheet and subtract the said amount (I am outlining the limitation and do not favor manual correction but drawing your attention for maybe rasing new issues that we can probably solve by automation in new questions)
Hope I have help you to see clearer in this pls do not hesitate to let me kow of your comments and suggestions. Finally as long as you don't activate the Delete worksheets then you will always have your data and whatever you input in commissions.
gowflow
Salesman-Split-comm-110913.xlsm
mdg1
Did you have a chance to test the last version posted ?
gowflow
Did you have a chance to test the last version posted ?
gowflow
Any news ???
gowflow
gowflow
ASKER
So gowflow...wasn't in the office much last week. I think we are good! can you give me instructions on how to get the code onto the excel document? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much.
Your welcome glad I could help. Tks for the grade
gowflow
gowflow
gowflow