How to generate multiple statements from single table?

[crimmo]

Hi,

I have a table with the following columns

Date of Sale
Sales Rep No.
Sales Rep Name
Client Name
Sale of Good Description
Gross
VAT
NET

I need to set up monthly statements for each Sales Rep. which will detail what they sold in a particular month so I can send them a statement of their commission due. In theory a sales rep may make 100's of sales although in reality they never seem to make more than 25, so each rep may have up to 25 sales in any one month. I need to layout a table so that it I input their sales rep no. which then populates their name etc. (easy using vlookup). However I need a further table which looks for this persons sales within a month and populates the statement. I can leave 30 lines available, which will be sufficient for 99% of months. I can have two fields on the statement sheet, where you can enter the last date of the previous month and the first date of the next month so that you can search for dates in between (i.e. the month I am looking). However where I am struggling is to write a formula that searches for sales within this date range in each of the 30 lines but never repeats a sale already in the list. For example, if sales person A has sales to x,y and z in January, how can line 1 show the x details, line 2 the y details, line 3 the z details and line 4-30 no details? I hope this makes sense as it is driving me mad!

Any help greatly appreciated.

Cheers

[dluhut]

Would you be able to have an sample attachment with desired result?

From your description, it seems that it can be done through array function. But still, would be better to have example rather than visualizing it

[crimmo]

Hi

Good idea. I have attached a slimmed down version. In the first sheet you can see a sample of the raw data in a table. The second sheet shows what a commission statement may look like. I have taken one sales person during one month (Amy Smith in January) and just hard coded in the answers. I have put cells in yellow that I think could be the drivers of the data while the other cells would populate automatically. I hope this makes it easier to follow. Thanks so much, really appreciate your help.

Cheers

[dluhut]

I know completely what you're trying to do here.

Quick question, what happens if the sales person, say amy smith, makes more than 12 sales in a month (you've prepared 12 rows of data).

If you want to have the capability of inserting new rows, VBA is the solution

[crimmo]

Good question, in the final version I was proposing 30 lines. I have checked back over last 6 months and I haven't seen anyone with more than 25, usually 5-15. I know I run the risk of missing out so maybe I could have the totals at the top and then have as many as I want below. The statement I sent is a *** off from our supplier statement so I could get something out quickly. However, I am very open to changing the layout of the statement.

[crimmo]

Yes, I agree an array is most likely solution but how do you detail each individual sale without repeating previous entries or having some massive if formula?

[shg]

Why not just autofilter the Datasheet page by Therapist Code?

[crimmo]

To a certain extent it is due to presentation but also I need to filter by date as well as therapist. Not sure the auto filter achieves that but not sure?

[shg]

You tried, and ...?

[crimmo]

One of the issues I came across with the autofill is that I need to total some columns but when I filter by name it still shows the overall total and not the total for just that person.

Any ideas on getting around that?