I have a protected worksheet that allows any user to insert new rows. Each
row has 2 formulas and 2 validation managed cells. When a user inserts a
row, the validation is copied, but the formulae are not. How can the
formulae be fromated to fill on inserted rows.
--
Riker1074

When you go to insert, copy the row above where you want to insert, then
right click, paste special formulas.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Riker1074" wrote:

> I have a protected worksheet that allows any user to insert new rows. Each
> row has 2 formulas and 2 validation managed cells. When a user inserts a
> row, the validation is copied, but the formulae are not. How can the
> formulae be fromated to fill on inserted rows.
> --
> Riker1074

Thanks Luke, but I didn't want to allow users to select locked cells.

Besides, the users of the file wouldn't know how to do what you suggested, I
really need a way to automate the process so it requires no action on the
part of the user. My target audience is office-illiterate
--
Riker1074


"Luke M" wrote:

> When you go to insert, copy the row above where you want to insert, then
> right click, paste special formulas.
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Riker1074" wrote:
>
> > I have a protected worksheet that allows any user to insert new rows. Each
> > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > row, the validation is copied, but the formulae are not. How can the
> > formulae be fromated to fill on inserted rows.
> > --
> > Riker1074

If you're using Excel 2003 or higher, Excel's "List" functionality (under the
Data menu) is great for inserting rows which retain the formulas and
formatting of the row directly above it without needing to copy & paste.

Just highlight your header row and data area and hit Ctrl+L to convert it to
an Excel List.

This function also converts the header row into an Auto Filter.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Riker1074" wrote:

> Thanks Luke, but I didn't want to allow users to select locked cells.
>
> Besides, the users of the file wouldn't know how to do what you suggested, I
> really need a way to automate the process so it requires no action on the
> part of the user. My target audience is office-illiterate
> --
> Riker1074
>
>
> "Luke M" wrote:
>
> > When you go to insert, copy the row above where you want to insert, then
> > right click, paste special formulas.
> > --
> > Best Regards,
> >
> > Luke M
> > *Remember to click "yes" if this post helped you!*
> >
> >
> > "Riker1074" wrote:
> >
> > > I have a protected worksheet that allows any user to insert new rows. Each
> > > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > > row, the validation is copied, but the formulae are not. How can the
> > > formulae be fromated to fill on inserted rows.
> > > --
> > > Riker1074

See David McRitchie's site for VBA method

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


Gord Dibben MS Excel MVP

On Mon, 22 Dec 2008 12:54:01 -0800, Riker1074
<(E-Mail Removed)> wrote:

>I have a protected worksheet that allows any user to insert new rows. Each
>row has 2 formulas and 2 validation managed cells. When a user inserts a
>row, the validation is copied, but the formulae are not. How can the
>formulae be fromated to fill on inserted rows.

KC, thank you for your suggestion, I tried to use the list function, but the
attempt was thwarted by the locked cell status. Excel would not allow itself
to copy to a locked cell.
--
Riker1074


"KC Rippstein" wrote:

> If you're using Excel 2003 or higher, Excel's "List" functionality (under the
> Data menu) is great for inserting rows which retain the formulas and
> formatting of the row directly above it without needing to copy & paste.
>
> Just highlight your header row and data area and hit Ctrl+L to convert it to
> an Excel List.
>
> This function also converts the header row into an Auto Filter.
> --
> Please remember to indicate when the post is answered so others can benefit
> from it later.
>
>
> "Riker1074" wrote:
>
> > Thanks Luke, but I didn't want to allow users to select locked cells.
> >
> > Besides, the users of the file wouldn't know how to do what you suggested, I
> > really need a way to automate the process so it requires no action on the
> > part of the user. My target audience is office-illiterate
> > --
> > Riker1074
> >
> >
> > "Luke M" wrote:
> >
> > > When you go to insert, copy the row above where you want to insert, then
> > > right click, paste special formulas.
> > > --
> > > Best Regards,
> > >
> > > Luke M
> > > *Remember to click "yes" if this post helped you!*
> > >
> > >
> > > "Riker1074" wrote:
> > >
> > > > I have a protected worksheet that allows any user to insert new rows. Each
> > > > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > > > row, the validation is copied, but the formulae are not. How can the
> > > > formulae be fromated to fill on inserted rows.
> > > > --
> > > > Riker1074

KC,

I also tried to use the list function paired with a validation. I created
the list off screen in a non-printing part of the sheet. I then targeted the
validation to each line of the list individually, unlocked the validated
cells and the list's cells, locked the worksheet and again excel stopped me.
This time it told me: You are attempting to move cells in a list. If I
focused on a cell in the list, no problem, except that the insertion wouldn't
add a row to the printable section of the sheet.

I know there must be a way to do this!
--
Riker1074


"KC Rippstein" wrote:

> If you're using Excel 2003 or higher, Excel's "List" functionality (under the
> Data menu) is great for inserting rows which retain the formulas and
> formatting of the row directly above it without needing to copy & paste.
>
> Just highlight your header row and data area and hit Ctrl+L to convert it to
> an Excel List.
>
> This function also converts the header row into an Auto Filter.
> --
> Please remember to indicate when the post is answered so others can benefit
> from it later.
>
>
> "Riker1074" wrote:
>
> > Thanks Luke, but I didn't want to allow users to select locked cells.
> >
> > Besides, the users of the file wouldn't know how to do what you suggested, I
> > really need a way to automate the process so it requires no action on the
> > part of the user. My target audience is office-illiterate
> > --
> > Riker1074
> >
> >
> > "Luke M" wrote:
> >
> > > When you go to insert, copy the row above where you want to insert, then
> > > right click, paste special formulas.
> > > --
> > > Best Regards,
> > >
> > > Luke M
> > > *Remember to click "yes" if this post helped you!*
> > >
> > >
> > > "Riker1074" wrote:
> > >
> > > > I have a protected worksheet that allows any user to insert new rows. Each
> > > > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > > > row, the validation is copied, but the formulae are not. How can the
> > > > formulae be fromated to fill on inserted rows.
> > > > --
> > > > Riker1074

Gord,

I tried the macro, it requires that the protection allow users to insert
rows. Without that permission a dialog box pops up reading: Error: 400. Of
course I allowed insert row functionality and, when I hit the button, "Error:
400".

I'm ready to give up. MS Office is trying to kill me!
--
Riker1074


"Gord Dibben" wrote:

> See David McRitchie's site for VBA method
>
> http://www.mvps.org/dmcritchie/excel/insrtrow.htm
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 22 Dec 2008 12:54:01 -0800, Riker1074
> <(E-Mail Removed)> wrote:
>
> >I have a protected worksheet that allows any user to insert new rows. Each
> >row has 2 formulas and 2 validation managed cells. When a user inserts a
> >row, the validation is copied, but the formulae are not. How can the
> >formulae be fromated to fill on inserted rows.
>
>

What if you just setup a macro button that behind the scenes turns off
protection, inserts a row in your list above where the cursor is located,
then turns protection back on? Since your password will be in the code,
you'll just want to apply a password to your VBA project as well so no one
can even see the code at all except you.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Riker1074" wrote:

> KC,
>
> I also tried to use the list function paired with a validation. I created
> the list off screen in a non-printing part of the sheet. I then targeted the
> validation to each line of the list individually, unlocked the validated
> cells and the list's cells, locked the worksheet and again excel stopped me.
> This time it told me: You are attempting to move cells in a list. If I
> focused on a cell in the list, no problem, except that the insertion wouldn't
> add a row to the printable section of the sheet.
>
> I know there must be a way to do this!
> --
> Riker1074
>
>
> "KC Rippstein" wrote:
>
> > If you're using Excel 2003 or higher, Excel's "List" functionality (under the
> > Data menu) is great for inserting rows which retain the formulas and
> > formatting of the row directly above it without needing to copy & paste.
> >
> > Just highlight your header row and data area and hit Ctrl+L to convert it to
> > an Excel List.
> >
> > This function also converts the header row into an Auto Filter.
> > --
> > Please remember to indicate when the post is answered so others can benefit
> > from it later.
> >
> >
> > "Riker1074" wrote:
> >
> > > Thanks Luke, but I didn't want to allow users to select locked cells.
> > >
> > > Besides, the users of the file wouldn't know how to do what you suggested, I
> > > really need a way to automate the process so it requires no action on the
> > > part of the user. My target audience is office-illiterate
> > > --
> > > Riker1074
> > >
> > >
> > > "Luke M" wrote:
> > >
> > > > When you go to insert, copy the row above where you want to insert, then
> > > > right click, paste special formulas.
> > > > --
> > > > Best Regards,
> > > >
> > > > Luke M
> > > > *Remember to click "yes" if this post helped you!*
> > > >
> > > >
> > > > "Riker1074" wrote:
> > > >
> > > > > I have a protected worksheet that allows any user to insert new rows. Each
> > > > > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > > > > row, the validation is copied, but the formulae are not. How can the
> > > > > formulae be fromated to fill on inserted rows.
> > > > > --
> > > > > Riker1074

KC,
You are a genius! I can't believe I didn't think of that.
--
Riker1074


"KC Rippstein" wrote:

> What if you just setup a macro button that behind the scenes turns off
> protection, inserts a row in your list above where the cursor is located,
> then turns protection back on? Since your password will be in the code,
> you'll just want to apply a password to your VBA project as well so no one
> can even see the code at all except you.
> --
> Please remember to indicate when the post is answered so others can benefit
> from it later.
>
>
> "Riker1074" wrote:
>
> > KC,
> >
> > I also tried to use the list function paired with a validation. I created
> > the list off screen in a non-printing part of the sheet. I then targeted the
> > validation to each line of the list individually, unlocked the validated
> > cells and the list's cells, locked the worksheet and again excel stopped me.
> > This time it told me: You are attempting to move cells in a list. If I
> > focused on a cell in the list, no problem, except that the insertion wouldn't
> > add a row to the printable section of the sheet.
> >
> > I know there must be a way to do this!
> > --
> > Riker1074
> >
> >
> > "KC Rippstein" wrote:
> >
> > > If you're using Excel 2003 or higher, Excel's "List" functionality (under the
> > > Data menu) is great for inserting rows which retain the formulas and
> > > formatting of the row directly above it without needing to copy & paste.
> > >
> > > Just highlight your header row and data area and hit Ctrl+L to convert it to
> > > an Excel List.
> > >
> > > This function also converts the header row into an Auto Filter.
> > > --
> > > Please remember to indicate when the post is answered so others can benefit
> > > from it later.
> > >
> > >
> > > "Riker1074" wrote:
> > >
> > > > Thanks Luke, but I didn't want to allow users to select locked cells.
> > > >
> > > > Besides, the users of the file wouldn't know how to do what you suggested, I
> > > > really need a way to automate the process so it requires no action on the
> > > > part of the user. My target audience is office-illiterate
> > > > --
> > > > Riker1074
> > > >
> > > >
> > > > "Luke M" wrote:
> > > >
> > > > > When you go to insert, copy the row above where you want to insert, then
> > > > > right click, paste special formulas.
> > > > > --
> > > > > Best Regards,
> > > > >
> > > > > Luke M
> > > > > *Remember to click "yes" if this post helped you!*
> > > > >
> > > > >
> > > > > "Riker1074" wrote:
> > > > >
> > > > > > I have a protected worksheet that allows any user to insert new rows. Each
> > > > > > row has 2 formulas and 2 validation managed cells. When a user inserts a
> > > > > > row, the validation is copied, but the formulae are not. How can the
> > > > > > formulae be fromated to fill on inserted rows.
> > > > > > --
> > > > > > Riker1074