Auto inserting extra row, while still totaling amount

[Benlinus815]

Hi There,

I work at a school canteen where the students constantly are filling out forms to owe us back, however many of them forget or choose not to pay them back.

I am trying to create a spreadsheet with 4 columns.

Column A - Student Name
Column B - Date
Column C - Amount the borrowed
Column D - Total of all their individual IOU's (Some are repeat offenders)

What I need to do under each students previous IOU, for Excel to insert a new row underneath (ready to enter their next offense) and for the total to keep adding up for every time a new row is added.

Example.

Student Name Date Amount Total

Joe Bloggs 4/9/16 $5.20
5/9/16 $3.00 $8.20

Harrison Ford 7/9/16 $2.50 $2.50


So, When Joe takes out another IOU on the 6th it would look like this. When adding the date into Joe's next cell an automatic row drops in and the total adds up also.


Student Name Date Amount Total

Joe Bloggs 4/9/16 $5.20
5/9/16 $3.00
6/9/16 $10.00 $18.20

Harrison Ford 7/9/16 $2.50 $2.50

Please assume that I know next to nothing about Excel so step by step instructions would be very helpful.

Thank you in advance.

[TheN]

Hi, there are multiple ways you could approach this.

First, you could use VBA to accomplish it in the way you are suggesting, to automatically add a row after each person's last offense. However, I do not suggest this route, macros are next to impossible for a new excel user to maintain.

Next, you could have them fill out their offenses as they happen, in chronological order. Then, on a different sheet, you could have a unique list of all the offenders, and have their total amount there. You could also have the amount of times they've borrowed or any other relevant information following their name, if you desired.

Another option, related to above would be to have a drop down menu with all the unique names (probably in alphabetical order) then when that name is selected, a table fills in with all their information. This solution could also be accomplished with a simple filter applied to your data, but the drop down option would look more professional.

You could do the second and third options at the same time if desired also. Or stick to the VBA required method as you first described it.

[Benlinus815]

Thanks for your reply. I am more than happy to learn VBA if someone is willing to give me step by steps on how to get the results I'm after.

Thanks, Trav