Lock a formula to a column of cells
 

I have a running balance total but as other users access this form they
inadvertently delete the formula or add more rows than have been allowed. I
can use data protection for the form but this then creates problem if they
need to add additional rows. Is there any way I can set the formula to appear
automatically in the cell such as data validation?

Lock a formula to a column of cells
 

This is not exactly what you are asking for... but you can lock only the
cell with formula by selecting all the cells in the worksheet, then select
"Format.Cells.Protection" and uncheck the "Locked" checkbox... then go to
the cell with your formula and select "Format.Cells.Protection" and check
the "Locked" checkbox to re-lock the cell with your formula.

Then go to Tools.Protection.Protect Worksheet" and check the box that says
"Insert Rows" under the heading "Allow all users" then all your users will
be able to insert rows and change all cells but your formula cell.

If you want to make sure the formula sums all rows above the row with your
formula (in case users insert extra rows), then you can use the following:
=SUM(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRES S(ROW()-1,COLUMN())))

"dragons_lair" wrote in message
...
I have a running balance total but as other users access this form they
inadvertently delete the formula or add more rows than have been allowed.
I
can use data protection for the form but this then creates problem if they
need to add additional rows. Is there any way I can set the formula to
appear
automatically in the cell such as data validation?

Lock a formula to a column of cells
 

Another option to Joe's formula where you can control the range of rows to
sum is:
=SUM(A$6:INDEX(A:A,ROW()-1))



"Joe Nastasi" wrote:

This is not exactly what you are asking for... but you can lock only the
cell with formula by selecting all the cells in the worksheet, then select
"Format.Cells.Protection" and uncheck the "Locked" checkbox... then go to
the cell with your formula and select "Format.Cells.Protection" and check
the "Locked" checkbox to re-lock the cell with your formula.

Then go to Tools.Protection.Protect Worksheet" and check the box that says
"Insert Rows" under the heading "Allow all users" then all your users will
be able to insert rows and change all cells but your formula cell.

If you want to make sure the formula sums all rows above the row with your
formula (in case users insert extra rows), then you can use the following:
=SUM(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRES S(ROW()-1,COLUMN())))

"dragons_lair" wrote in message
...
I have a running balance total but as other users access this form they
inadvertently delete the formula or add more rows than have been allowed.
I
can use data protection for the form but this then creates problem if they
need to add additional rows. Is there any way I can set the formula to
appear
automatically in the cell such as data validation?

Lock a formula to a column of cells
 

Thanks for the info, not quite what I was after but I did learn a new formula
which has proved very useful
--
Dragonette


"Joe Nastasi" wrote:

This is not exactly what you are asking for... but you can lock only the
cell with formula by selecting all the cells in the worksheet, then select
"Format.Cells.Protection" and uncheck the "Locked" checkbox... then go to
the cell with your formula and select "Format.Cells.Protection" and check
the "Locked" checkbox to re-lock the cell with your formula.

Then go to Tools.Protection.Protect Worksheet" and check the box that says
"Insert Rows" under the heading "Allow all users" then all your users will
be able to insert rows and change all cells but your formula cell.

If you want to make sure the formula sums all rows above the row with your
formula (in case users insert extra rows), then you can use the following:
=SUM(INDIRECT(ADDRESS(1,COLUMN())):INDIRECT(ADDRES S(ROW()-1,COLUMN())))

"dragons_lair" wrote in message
...
I have a running balance total but as other users access this form they
inadvertently delete the formula or add more rows than have been allowed.
I
can use data protection for the form but this then creates problem if they
need to add additional rows. Is there any way I can set the formula to
appear
automatically in the cell such as data validation?