none
When adding a row in a defined table, my formula does not replicate down to the new row. Why? RRS feed

  • Question

  • I had originally posted this question in a different forum (https://social.technet.microsoft.com/Forums/office/en-US/4060c1a6-7857-458c-ae29-8170b0c15116/when-adding-a-row-in-a-defined-table-my-formula-does-not-replicate-down-to-the-new-row-why?forum=excel), but I was told to post this question here. 


    This was the response from the other thread 'you can use code to get the result, I have confirmed with developers for Excel.'

    I've attached an image for clarification. Basically the last column in the table is calculating the running profit/total. The first row in the table, the formula is different than the other rows because it is the first row in the table. See the image.

    My question is why doesn't' the formula carry down when a new row is added? You can see how the file/options/formulas screen is set up in the attached image. The response from the other forum stated 'you can use code to get the result.' I don't know what this code would be. Does anyone know?

    Also my other question is why do the other formulas in other columns carry down but the above formula for running profit doesn't?


    Keith Aul

    Wednesday, August 10, 2016 9:58 PM

Answers

  • Hi KeithAul,

    First of all, I think that you are using User Interface of Excel.

    so the thread is related with Excel Forum. I don't know why they redirect you here.

    if you are not able to populate formula in new row then it may be a issue related with an Excel Application.

    First I recommend you to check the settings mentioned below.

    If you want to enable formula replication altogether, you can do that by changing one of Excel’s Options. In the Excel Options window, choose Proofing and then click the AutoCorrect Options button to open the AutoCorrect dialog box. In the AutoCorrect dialog box, check the box next to Fill formulas in tables to create calculated columns and click OK. This action enable formula replication in tables.

    The other thing I had found that other Excel Users are also had same issue in past.

    Why won't my new table rows auto-fill down with my formulae?

    Copying formula to the next row when inserting a new row

    and now talk about VBA Code. As you are redirected here you can use line of code below to set formula for a Range in column.

    Range("M3") = "=G3&"",""&L3": Range("M3:M" & LastRow).FillDown
    
    
    
    Alternatively you can also use code below.
    
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
            Application.EnableEvents = False
        lastrow = Range("C65536").End(xlUp).Row
        Range("A74:B74").AutoFill Destination:=Range("A74:B" & lastrow), Type:=xlFillDefault
        Application.EnableEvents = True
    End If
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 11, 2016 2:39 AM
    Moderator