Insert rows in table with protected/locked columns with formulae
Results 1 to 2 of 2

Thread: Insert rows in table with protected/locked columns with formulae
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Insert rows in table with protected/locked columns with formulae

    Hi all!
    I have a tablein Excel in which some columns need to be locked, and other columns need to beunlocked so people can enter data there. The columns that need to stay lockedhave formulae in each cell which use data that is entered in the unlockedcolumns.
    Peopleusing the table should be able to insert rows whilst some of the columns remainlocked and the formulae are copied in the newly inserted rows.

    I am unableto figure out how to allow for this. Therefore, my question is how can youinsert rows in a table where some of the columns are locked? It is necessarythat the formulae in the locked columns are copied when a new row is inserted.
    Does this require a macro?


    Any helpwould be much appreciated!
    Thanks,
    Lies

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,628
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Insert rows in table with protected/locked columns with formulae

    If your data is in a table, and the sheet is not protected, then formulas will be copied into the blank row if you right-click on the table and select Insert | Table Rows Above.

    You could leave the columns unlocked and use a macro to refresh the formulas in the old and new rows based on a one more workbook/worksheet events (like workbook save or worksheet activate, or calculate). There are other events available that trigger more often, but you probably don't want to have the formulas refreshed each time the selection was changed.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •