Add Rows in Tables B and C based off entries in Table A

[dunganbrendan]

Hello all,

I have two tables (call them Tables B and C) full of formula calculations that interpret data submitted on a customer input sheet. The customer input sheet has its own table (call this one Table A. Right now the tables each have 500 rows and the spreadsheet is 600kb. The size is not bad but to give a template to virtually all customers would mean 2500 rows in all tables which comes out to be >1mb.

We want to start the tables at 100 rows and only expand the rows of Tables B and C to the number of rows in Table A. So as a customer is entering data into row 101, I would like a row to be added to the bottom of Tables B and C and the formulas within those tables to copy down into the new row. As mentioned, some customers have >2000 entries so their documents will be heavy. But we have many customers who have <200 entries and who would do much better with a smaller template.

Most customers pull from reports instead of doing manual data entry, so it would be best if, for example, a customer uploads 800 rows full of data into Table A, Tables B and C would automatically resize to have 800 rows with formulas.

Any help or advice is greatly appreciated! Thank you!

 

[severynm]

Hi,

For one of my worksheets, I had written code to rectify the difference in table length between two tables on the same worksheet. If there is a difference in length, I add rows to the second table until they are the same length. It sounds like maybe you should add this to the Worksheet_Change event, and modify the code below to only execute if a new row was added to your source table.

    Dim lengthDifference As Integer
    lengthDifference = SheetName.ListObjects("Table1Name").ListRows.Count - SheetName.ListObjects("Table2Name").ListRows.Count
    If lengthDifference > 0 Then
        Do While lengthDifference > 0
            SheetName.ListObjects("Table2Name").ListRows.Add
            lengthDifference = lengthDifference - 1
        Loop
    End If
End Sub

I cannot say how quickly this will execute if you are trying to add 100s of rows in one go, but if you are only needing to add a few rows at a time as data is added, it'll be quick.