Forum Discussion

José Neves's avatar
José Neves
Copper Contributor
Sep 29, 2017

inserting new lines

Team, I´m creating a table where i have a line with cells that users can fill and other to display the results (in grey with formulas). The idea is to give that table with a single line and, whenev...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 30, 2017

Hi Jose,

 

If you are on Excel 2007 or any later you may create the table, here is how

https://support.office.com/en-us/article/Create-or-delete-an-Excel-table-e81aa349-b006-4f8a-9806-5af9df0ac664?ui=en-US&rs=en-US&ad=US

http://www.excel-easy.com/data-analysis/tables.html

 

Within the table you may use refrence on column name instaed of refrence on cell address, here is how it works

https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

 

In your case the table could look like

and the formulas within it will be

=NETWORKDAYS([@Start],[@End])
=[@End]-[@Start]-[@[Work day]]+1
=[@[Work day]]+[@[Non-Work day]]
=[@[0800-2000]]

Mentioned setting shall work by default, you may find them at

File->Options->Proofing->AutoCorrect Options->AutoFormat As You Type

 

As soon as you enter any value into the cell under last row of the table the table row with formulas if any will be added automatically.

 

In the attached file is the sample in next to your sheet

 

 

José Neves's avatar
José Neves
Copper Contributor
Sep 30, 2017

Sergei,

Once again thank you very very much.

One more help please.

I haven't yet understood what is the "mechanism" to trigger the creation of the new line. On the new sample I'm sending you the cells "Event" should be the one trigering the new line. However, the content/value of that cell (text) is not used for any formula.

Again the grey cells will be locked for edit from end users.

 

Thanks

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 30, 2017

    Jose, i missed the protection. Yes, to unprotect for new column you need the macro. But in any case better to use Tables.

     

    I'll update your sample in a while.

    • José Neves's avatar
      José Neves
      Copper Contributor
      Sep 30, 2017
      Thanks
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 30, 2017

        Jose, I took this https://www.extendoffice.com/documents/excel/4382-excel-lock-sheet-but-keep-tables-expandable.html as the sample, here is quite good explanation how to do.

         

        For your concrete table the macro is

        Sub Button1_Click()
Dim pswStr As String
    pswStr = "123"

On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
Range("TimeTable[[#Headers],[Extended hours (08:00-20:00)]]").Select
Selection.End(xlDown).Select
Selection.Offset(1, -8).Select
ActiveCell.FormulaR1C1 = "xpto"
ActiveSheet.Protect DrawingObjects:=False, _
    Contents:=True, Scenarios:=False, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingColumns:=True, _
    AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
    AllowDeletingColumns:=True, AllowDeletingRows:=True, _
    AllowSorting:=True, AllowFiltering:=True, _
    AllowUsingPivotTables:=True
Selection.ClearContents
Application.ScreenUpdating = True

End Sub

        In addition on formula cells i hided formulas for the protection sheet. (check Hidden in Protection tab of cells formatting).

         

        If you unprotect the sheet and change table unlock cells without formula before protection only within the table.

        Not sure how to do without password, looks more complex, at least for me.

         

        Sample file is in archive, macro enabled files are declined here.

         

        You may add your vote for adding above functionality without macro workaround https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows

         

        Above is one of patterns, you may find more, idea is similar. 

         

         

Resources