Automatically adding a complete row to a second worksheet

Good day. I'm a new poster with limited Excel 2013 experience and was hoping for some assistance. (Being a newbie, I inadvertently posted this to an old thread and was advised to start a new one, so here goes!) I'm not familiar with VBA and would like to tackle this without VBA if possible. Here's my scenario (spreadsheet example attached)...

I have two worksheets in a workbook. Sheet 1 is source data of teammates (index number, first and last name, email, phone, etc.) listed alphabetically. Sheet 2 is our teammate training record. It imports the first 3 columns from Sheet 1 (index number, first and last name) using VLOOKUP, and I have 12 additional columns for me to enter their monthly training attendance as the year goes on. Now, a new teammate joins, so on Sheet 1, I insert a new row at the appropriate place to add that new person's basic information, and on Sheet 2 a new row is automatically added for that person in columns 1-3 (index, first and last name) with VLOOKUP and I can start entering their monthly training.

Here's the problem... Let's say the new person is added somewhere in the middle of the alphabet on Sheet 1. As it stands right now, Sheet 2 adds the new person's info in columns 1-3 and the remaining Sheet 2 columns are unaffected. So I have to manually go into Sheet 2 and insert new cells for the new person, pushing appropriate cells down to realign the existing data with the correct person. How do I make it automatically add a complete row across for the new employee on sheet 2?

Test: In the attached spreadsheet Sheet 1, I insert row between existing row 5 and 6 for a new teammate named "TEST". I insert "TEST" into cell B6 and C6. I repair the index column by dragging A6 downward. Now in Sheet 2, I see that Mr. TEST is inserted in columns B and C, but the rest of that row (columns D through O) containing Mr. Whiskey's data haven't moved down, so I have to insert cells in those columns to drive Mr Whiskey's data back down to his row.

Any assistance is greatly appreciated. Thank you.

-D.

kshnoije,

Rather than linking the sheets with VLOOKUPS or Index/Match, why not just add the new record to the first free row on sheet 1, copy the three columns to the first free row on sheet 2, then sort both sheets alphabetically by Cols B and C?

Ochimus

Ochimus, thank you for your reply. This two sheet example is a small part of a larger project and knowing how to achieve this will greatly simplify the development and maintenance. Thanks again!

kshnoije

kshnoije,

You said at the outset that you would like to tackle this without VBA if possible, but it really is the easiest way to achieve what you want.

Attached Code works as follows:

In DATABASE sheet, enter new First and Family name in first empty row, complete the remaining details or leave them blank.
Press the ADD button in row 1.

Code "resets" the Index Number, then copies the number and two Names onto the next free row of the Training sheet.

Code then resorts both sheets into Alphabetic order.

I have added comments so you can follow the process:

Please Login or Register  to view this content.

Hope this helps

Ochimus