Automatically insert formatted dropdown rows as per cell value

[dnkinc]

I very much need some help with my current project as my knowledge of excel is limited.

I have been asked to create a spreadsheet for a survey.

I have a dropdown table where the surveyor can choose from each column, however, I need the sheet to insert the formulated rows as per the sites number of power points i.e. Site 1 has 45 points so will need 45 rows inserted automatically to be completed.  Site 2 has 13 point so 13 rows and so on.

I also need the number of points to be quite high depending on building size so could go into the hundreds.

I also need a total at the bottom of one of the columns.

As the table above shows, the total AP's for this particular site is 63 which is a v lookup of a job number.  I need 63 rows then inserted as per row 2.  Row 1 shows the information already selected that the surveyor will choose from the dropdowns.

Is this possible?

Many thanks

Nicki

[#2]

@dnkinc 

Hello there, please see attached document with proposed solution. Hope it works as intended.

It basically reads the value of a certain cell to understand how many rows to add, then it adds that amount of rows with specific text/formula. Note that you may change your sheet name, your table name, and the cell reference indicating how many rows to add.

Martin

[#3]

@Martin_Angosto 

Many thanks for your response Martin, however when I open the file the formulas are missing?

Is there any way that you can type the formula in here please?

MT

Nicki

[#4]

@dnkinc 

I did not use any formulas. This functionality is using VBA code (macros). To be able to see it, you just have to enable macros on this particular file. Once done, enable the "Developer" tab for the ribbon by going to File > Options > Customized Ribbon > Select Developer on the right.

Then, go to Developer > Macros and run the unique macro that it exists in the file. It automatically adds the rows based on the number value on cell B3.

If you prefer, I can share the code in here as well:

Sub AddRowsTotalAP()

Dim TotalAP As ListObject
Dim rowCount As Integer
Dim i As Integer

Set TotalAP = ThisWorkbook.Sheets("MySheet").ListObjects("TotalAP")

rowCount = ThisWorkbook.Sheets("MySheet").Range("B3").Value

For i = 1 To rowCount

    TotalAP.ListRows.Add
    
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 1).Value = "AP" & i
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 2).Value = "Location"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 3).Value = "Cable"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 4).Value = "Metres"
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 5).Value = ""
TotalAP.DataBodyRange.Cells(TotalAP.ListRows.Count, 6).Formula = "=INDIRECT(""R[0]C[-2]"",FALSE)*INDIRECT(""R[0]C[-1]"",FALSE)"

Next i

End Sub

Any further comments do not hesitate to reply!