Forum Discussion

dnkinc's avatar
dnkinc
Copper Contributor
May 04, 2024

Automatically insert formatted dropdown rows as per cell value

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

excel
Formulas and Functions
Macros and VBA
  • Martin_Angosto's avatar
    Martin_Angosto
    Iron Contributor
    May 04, 2024

    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

    • dnkinc's avatar
      dnkinc
      Copper Contributor
      May 07, 2024

      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

      • Martin_Angosto's avatar
        Martin_Angosto
        Iron Contributor
        May 07, 2024

        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!

Resources