Accounting Guru Needed / Excel or Programming

I am trying to use excel as an input/output for generating agent commissions - or if someone knows of a program that will do this faster that is not to pricy that would be great. Here's exactly what I'm trying to accomplish.

I would like to key in a policy holder's name, social, and the date of the application in cells A1, B1, and C1 (or row 2 using headers in row 1).
In Cell's D1 and E1 would be the writing agents name and social or ID number.
In Cell's F1 would be agents assumed commission at 100%, in cell G1 would be commission from F1 based on an 80% pay, and then H1 would be the same F1 commission based on the remaining 20% balance. column's H & G are not really a necessity.

What I am trying to do is generate a program or formula, that will take all of row 1 using the variable of the agents name (Cell D1) to generate a new sheet that will be used for the commission statement. It will total all payable commissions so that all I have to do is go in and hit Print and we are done.

What I use now is a master excel where I input all the necessary info, and copy paste it over. In this business that is very time consuming.

If I could draw a screen It would be something similar to a program where I input all the information and it will output the statement with payable compensation with the specified agents name and his social showing all his policy holders. Then a Year to Date statement that I can print 6 months or 12 months later showing his full pay model.

Basically have box one is the name of the policy holder, box 2 is the policy holder's social, box 3 is the policy holder's application signature date, box 4 is the agents name, box 5 is the agents social or ID number, and box 6 is his commission percentage (boxes 7 and 8 may or may not be necessary but could be floated if not in use) and then the output would generate to a 1 page statement setup.

All help would be appreciated thank you! [email protected]

Anyone know what I'm asking about or does this seem a bit to complicated, please help thanks!

On second worksheet:
I1 : agents name
I2 : =MATCH(I$1,Sheet1!D:D,0)
I3 : =MATCH(I$1,OFFSET(Sheet1!D$1,I2,0,10000),0)+I2
replicate down as required

A1 : =IF(ISNA($I2),"",INDEX(Sheet1!A:A,$I2,1,1))

replicate across to column H and down as required

Hide column I

Mark.

Originally Posted by Mark@Work

On second worksheet:
I1 : agents name
I2 : =MATCH(I$1,Sheet1!D:D,0)
I3 : =MATCH(I$1,OFFSET(Sheet1!D$1,I2,0,10000),0)+I2
replicate down as required

A1 : =IF(ISNA($I2),"",INDEX(Sheet1!A:A,$I2,1,1))

replicate across to column H and down as required

Hide column I

Mark.

Mark does all the information from the master need to start on row 1?
When I input the codes on sheet 2 it pops up to reference the file, I choose the workbook but then do I do it by the "Master" worksheet or by the variable of the agents name?
When I input the code in I1 on sheet 2 does it need to read "agents name" or does it need to read "INS Marketing" if the agent is ins marketing?

Sorry for being so uninformed but i don't usually get this advanced in excel thanks!

D