Formula will not auto update when sheet is protected

[sheelamk]

i have created table, formula is in specific cell in the table,i want to lock this cell ,,how to do this ..and also formula should automatically come when new row is inserted

 

[Belleke]

Where is your example???????????????,

 

[Belleke]

also formula should automatically come when new row is inserted

Use a Table

 

[Monty]

i have created table, formula is in specific cell in the table,i want to lock this cell ,,how to do this ..and also formula should automatically come when new row is inserted

Hello Sheelaamk


Trying to explain from scratch, in case if you are not aware.

To lock a specific cell containing a formula within a table and ensure the formula is automatically applied to new rows, you can follow these steps:

1. Lock the Cell:
- Select the cell containing the formula within the table.
- Right-click and choose "Format Cells."
- Go to the "Protection" tab and check "Locked."
- Click "OK."

2. Protect the Worksheet:
- Go to the "Review" tab in the Excel ribbon.
- Click on "Protect Sheet."
- Set a password if needed and specify any additional options.
- Click "OK."

Now, the cell with the formula is locked, and the worksheet is protected. However, this alone won't automatically apply the formula to new rows.

3. Apply Formula to New Rows Automatically:
- You can use a Table (ListObject) and structured references for this purpose.
- Ensure your table has a header row, and your formula is in a column within the table.
- When you add a new row, Excel should automatically copy the formula from the header row to the new row.

For example, if your formula is in cell B2, and your table has a header row, the formula in the header cell B2 should automatically be applied to new rows in the column.

Remember to protect the worksheet to prevent users from editing locked cells. If you need to add new rows, you'll have to unprotect the sheet, insert rows, and then protect it again.

All the Best!