Insert rows in table with protected/locked columns with formulae

[LiesvanHouw]

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

 

[pbornemeier]

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.