Hello,
I have this spreadsheet at work that I am attempting to add some macros/formulas to so that it does some processes automatically.
I have come up with a few solutions, but I am stuck on one problem at the moment.
I have figured out how to automatically pull out the initials of the salesperson (in the example below, JLS), and have it show a list of all the salespeople, while removing duplicates/blanks. Example on the right of the table below.
I have also figured out how to have it automatically pull out and total all of the deduction amounts, (which are the red numbers in the amount column). Example of this is also shown to the right on the table below.
What I need help with, is this: I want to have it automatically pull out and total the deductions (red numbers) that are associated with each salesperson, and show this next to each salespersons initials in the list to the right. I can't just have it total the amount column, as that would give me an incorrect total. I need just the deductions, for each salesperson.
The problem I am having is that, in the spreadsheet, rows 2 & 3 are merged in all columns, except the narrative column. Added to this is the fact that the deduction amount is not in the same row as the salespersons initials.
A side note on this: The data changes constantly. This spreadsheet is used for each invoice. It is cleared out after each invoice is pasted in and a copy sent to the appropriate salespeople. So the data in the automatically generated lists will be changing every time the sheet is cleared and the information from a new invoice is added. Not sure if that will affect how this problem is solved, but I wanted as much information available as possible.
I am currently stumped...but still trying to figure this out.
Any suggestions would be greatly appreciated.
<tbody>
</tbody>
Thank you,
I have this spreadsheet at work that I am attempting to add some macros/formulas to so that it does some processes automatically.
I have come up with a few solutions, but I am stuck on one problem at the moment.
I have figured out how to automatically pull out the initials of the salesperson (in the example below, JLS), and have it show a list of all the salespeople, while removing duplicates/blanks. Example on the right of the table below.
I have also figured out how to have it automatically pull out and total all of the deduction amounts, (which are the red numbers in the amount column). Example of this is also shown to the right on the table below.
What I need help with, is this: I want to have it automatically pull out and total the deductions (red numbers) that are associated with each salesperson, and show this next to each salespersons initials in the list to the right. I can't just have it total the amount column, as that would give me an incorrect total. I need just the deductions, for each salesperson.
The problem I am having is that, in the spreadsheet, rows 2 & 3 are merged in all columns, except the narrative column. Added to this is the fact that the deduction amount is not in the same row as the salespersons initials.
A side note on this: The data changes constantly. This spreadsheet is used for each invoice. It is cleared out after each invoice is pasted in and a copy sent to the appropriate salespeople. So the data in the automatically generated lists will be changing every time the sheet is cleared and the information from a new invoice is added. Not sure if that will affect how this problem is solved, but I wanted as much information available as possible.
I am currently stumped...but still trying to figure this out.
Any suggestions would be greatly appreciated.
Date | Inv ID | Task | Initials | Task | Narrative | Code | Units | Rate | Amount | Total Deductions ($): | ($14.10) | Salespeople | |||||||
1 | 9/5/17 | 12345678 | L123 | JLS | Fee | This is what was done for the customer. | U | 0.40 | $235.00 | $94.00 | Total Number of Deductions: | 1 | JLS | ||||||
2 | (Type of Deduction) | GSP | |||||||||||||||||
3 | This is where the customer states why they feel a deduction is in order. | ($14.10) | FDR |
<tbody>
</tbody>
Thank you,