Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto fill formulae when inserting rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro auto inserting rows and formulas | Excel Discussion (Misc queries) | |||
auto fill rows with months based on cell value | Excel Discussion (Misc queries) | |||
auto fill rows in Excel 2007 | Excel Discussion (Misc queries) | |||
Inserting new row but keeping formulae | Excel Worksheet Functions | |||
inserting formulae | Excel Worksheet Functions |