I have a spreadsheet which has some complicated formulae in some cells, so I
want to lock and protect those cells to prevent other users from spoiling
them.

However, one of the things these users need to do is to insert rows to add
new data in the correct place. I would like the formulae from the
corresponding cells above the new row to be automatically copied. The only
way to get the formulae in that I know is to use the "fill handle" but you
cannot do that when the cell is locked.

Is there another way?

Hi Ian,

If you and your users are all using Excel 2002 or later, you can protect the
worksheet but still allow users to insert rows. Have a look at the options
when you select protect worksheet from the menu.

Hope this helps,

Mark


"Ian Goodhardt" wrote:

I have a spreadsheet which has some complicated formulae in some cells, so I
want to lock and protect those cells to prevent other users from spoiling
them.

However, one of the things these users need to do is to insert rows to add
new data in the correct place. I would like the formulae from the
corresponding cells above the new row to be automatically copied. The only
way to get the formulae in that I know is to use the "fill handle" but you
cannot do that when the cell is locked.

Is there another way?

Hi Mark

Yes - I know you can allow them to insert rows, but how can you then copy
the formulae from the protected cells in the row above (or below) the new
row? I was hoping this could be done automatically.

Ian

"Mark Hone" wrote:

Hi Ian,

If you and your users are all using Excel 2002 or later, you can protect the
worksheet but still allow users to insert rows. Have a look at the options
when you select protect worksheet from the menu.

Hope this helps,

Mark


"Ian Goodhardt" wrote:

I have a spreadsheet which has some complicated formulae in some cells, so I
want to lock and protect those cells to prevent other users from spoiling
them.

However, one of the things these users need to do is to insert rows to add
new data in the correct place. I would like the formulae from the
corresponding cells above the new row to be automatically copied. The only
way to get the formulae in that I know is to use the "fill handle" but you
cannot do that when the cell is locked.

Is there another way?

?B?SWFuIEdvb2RoYXJkdA==?= wrote

Hi Mark

Yes - I know you can allow them to insert rows, but how can you then
copy the formulae from the protected cells in the row above (or below)
the new row? I was hoping this could be done automatically.

Ian

"Mark Hone" wrote:

Hi Ian,

If you and your users are all using Excel 2002 or later, you can
protect the worksheet but still allow users to insert rows. Have a
look at the options when you select protect worksheet from the menu.

Hope this helps,

Mark


"Ian Goodhardt" wrote:

I have a spreadsheet which has some complicated formulae in some
cells, so I want to lock and protect those cells to prevent other
users from spoiling them.

However, one of the things these users need to do is to insert rows
to add new data in the correct place. I would like the formulae
from the corresponding cells above the new row to be automatically
copied. The only way to get the formulae in that I know is to use
the "fill handle" but you cannot do that when the cell is locked.

Is there another way?



Have a look he (it ain't easy, but doable)

http://www.mvps.org/dmcritchie/excel/insrtrow.htm


--
David