Dynamically expand an excel table based on entries in another table

Question

I'm setting up a spreadsheet with the goal of allowing users to enter data by employee and date. I'd like to create a dynamic table that expands based on entries into another table. E.g., can I automatically expand TABLE1 based on new employees entered into TABLE2 to look like TABLE3, making TABLE3 ready for new user-input into the "Value" column?

TABLE 1

TABLE 2

TABLE 3

Solution

Not the best way to be doing things, but this might help you, or at least point you in the right direction with what you are trying to do.

If Table 1 is in Sheet1 and Table 2 in Sheet2, when new rows are added into Table 2, the following code will copy that name and paste it three times into Table 1 of Sheet 1 and then copy the dates from the records above, in this case Bill's dates.

Place the following code under Sheet 2 on Worksheet Change event, like shown in the picture below:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
LastRow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row 'check the last row on Sheet 2 Table 2
LastRow1 = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row + 1 'Check the last row on Sheet1 Table 1

If Target.Row = LastRow Then    'if a new row is added to Table 2
    Sheet1.Cells(LastRow1, Target.Column).Value = Target.Value 'copy their name to table 1 three times
    Sheet1.Cells(LastRow1 + 1, Target.Column).Value = Target.Value
    Sheet1.Cells(LastRow1 + 2, Target.Column).Value = Target.Value

    Sheet1.Cells(LastRow1, 2).Value = Sheet1.Cells(LastRow1, 2).Offset(-3, 0).Value 'copy the Date from the date above on Sheet1
    Sheet1.Cells(LastRow1 + 1, 2).Value = Sheet1.Cells(LastRow1 + 1, 2).Offset(-3, 0).Value
    Sheet1.Cells(LastRow1 + 2, 2).Value = Sheet1.Cells(LastRow1 + 2, 2).Offset(-3, 0).Value
End If
End Sub