Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Hi my name is susy,
I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
You would have to do it with a macro - record a macro (in relative
addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
HI,.........went to hlp to find outwhat is macro and what can I do with it
but the help explanatin are vague. I'm not familair to all th epossibilities in Excell. For example help told me to record a code,.......what do they mean with code, formulas? and if so what fomula should I apply in thiss case? "Pete_UK" wrote: You would have to do it with a macro - record a macro (in relative addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
They mean Visual Basic code to program the spreadsheet to do a certain
behavior. In Excel 2003 and earlier, this functionality is found under Tools-Macros. In Excel 2007, you will find it under the Developer tab, which is hidden by default and must be enabled under Excel Options. When you use the "record macro" functionality, it will automatically generate Visual Basic code, also providing you an option to assign a shortcut key for this code. During recording, Excel will keep track of everything you do, which will later enable you to repeat these actions simply by running the macro or using the shortcut key combination that you specified. Many advanced users prefer to do this manually. For example, follow this sequence of steps: 1. In your workbook (save a copy), press Alt+F11. You will see the Visual Basic editor. 2. In VBE, under the Insert menu, choose Module. 3. Paste the following code: Public Sub InsertRow() ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown End Sub 4. Close the VBE to return back to Excel. 5. In Excel, press Alt+F8 to show the Macros menu. 6. Select one called InsertRow, and press Run. A row will be automatically inserted after your current cursor position. 7. Press Alt+F8 again, select InsertRow, and click on Options. 8. Go into the small shortcut key textbox next to Ctrl+ and press Shift +R. 9. Press OK, then Cancel to close the Macros menu 10. In Excel, press Ctrl+Shift+R. A row will be automatically inserted after your current cursor position. Hope this helps. On Oct 30, 3:39 pm, susy wrote: HI,.........went to hlp to find outwhat is macro and what can I do with it but the help explanatin are vague. I'm not familair to all th epossibilities in Excell. For example help told me to record a code,.......what do they mean with code, formulas? and if so what fomula should I apply in thiss case? "Pete_UK" wrote: You would have to do it with a macro - record a macro (in relative addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Hello,
You can protect the worksheet and still allow others to insert rows. If you are in Outlook 2003: 1. Unlock and unprotect all cells (Ctrl-A, then Ctrl-1, Protection tab, uncheck 'Locked' and 'Hidden') 2. Select the formula cells you want to protect, hit Ctrl-1, Protection tab, check 'Locked' and 'Hidden') 3. When protecting your worksheet, under 'Allow all users of this worksheet to", select 'Insert rows' HTH, JP On Oct 5, 1:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Hi thanx for replying to my post,............
I record a macro to insert a row it did function well and did record everything I did. But the problem was that it did copy the exact formula from Above. I did discover that without macro(i GUESS IT WAS WITHOUT MACRO) when i insert a row manually it did copy the formulas from above ,....but only when I insert som valeu inside of it . Can I just keep it like that and Protect the cell? "ilia" wrote: They mean Visual Basic code to program the spreadsheet to do a certain behavior. In Excel 2003 and earlier, this functionality is found under Tools-Macros. In Excel 2007, you will find it under the Developer tab, which is hidden by default and must be enabled under Excel Options. When you use the "record macro" functionality, it will automatically generate Visual Basic code, also providing you an option to assign a shortcut key for this code. During recording, Excel will keep track of everything you do, which will later enable you to repeat these actions simply by running the macro or using the shortcut key combination that you specified. Many advanced users prefer to do this manually. For example, follow this sequence of steps: 1. In your workbook (save a copy), press Alt+F11. You will see the Visual Basic editor. 2. In VBE, under the Insert menu, choose Module. 3. Paste the following code: Public Sub InsertRow() ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert Shift:=xlDown End Sub 4. Close the VBE to return back to Excel. 5. In Excel, press Alt+F8 to show the Macros menu. 6. Select one called InsertRow, and press Run. A row will be automatically inserted after your current cursor position. 7. Press Alt+F8 again, select InsertRow, and click on Options. 8. Go into the small shortcut key textbox next to Ctrl+ and press Shift +R. 9. Press OK, then Cancel to close the Macros menu 10. In Excel, press Ctrl+Shift+R. A row will be automatically inserted after your current cursor position. Hope this helps. On Oct 30, 3:39 pm, susy wrote: HI,.........went to hlp to find outwhat is macro and what can I do with it but the help explanatin are vague. I'm not familair to all th epossibilities in Excell. For example help told me to record a code,.......what do they mean with code, formulas? and if so what fomula should I apply in thiss case? "Pete_UK" wrote: You would have to do it with a macro - record a macro (in relative addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
I'm confused, you're asking if you can allow Excel to insert formulas
for you? Of course you can. This page might help you, if you want to understand this feature better. http://support.microsoft.com/kb/231002 HTH, JP On Nov 5, 2:31 pm, susy wrote: Hi thanx for replying to my post,............ I record a macro to insert a row it did function well and did record everything I did. But the problem was that it did copy the exact formula from Above. I did discover that without macro(i GUESS IT WAS WITHOUT MACRO) when i insert a row manually it did copy the formulas from above ,....but only when I insert som valeu inside of it . Can I just keep it like that and Protect the cell? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Sorry for the confusuing but I'm wanted to ask
those Excell copy automatically the formulas from the row above, so I can leave it with a macro? or is this something I could have programmed with out knowing? but I'll check you link thnx again 4 the help susy "JP" wrote: I'm confused, you're asking if you can allow Excel to insert formulas for you? Of course you can. This page might help you, if you want to understand this feature better. http://support.microsoft.com/kb/231002 HTH, JP On Nov 5, 2:31 pm, susy wrote: Hi thanx for replying to my post,............ I record a macro to insert a row it did function well and did record everything I did. But the problem was that it did copy the exact formula from Above. I did discover that without macro(i GUESS IT WAS WITHOUT MACRO) when i insert a row manually it did copy the formulas from above ,....but only when I insert som valeu inside of it . Can I just keep it like that and Protect the cell? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Hi Pete_UK,
just a questions if you compute a macro once and you delete it would you have to "disactived" it some how to aviod it recoding later one another action you take? Because I try to recod a macro but after I delete it and reopened te document excel ask me about recording a macro susy "Pete_UK" wrote: You would have to do it with a macro - record a macro (in relative addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
You must also delete the module the macro was deleted from.
See Debra Dalgleish's site for more info. http://www.contextures.on.ca/xlfaqMac.html#NoMacros Gord Dibben MS Excel MVP On Tue, 13 Nov 2007 10:51:01 -0800, susy wrote: Hi Pete_UK, just a questions if you compute a macro once and you delete it would you have to "disactived" it some how to aviod it recoding later one another action you take? Because I try to recod a macro but after I delete it and reopened te document excel ask me about recording a macro susy "Pete_UK" wrote: You would have to do it with a macro - record a macro (in relative addressing mode) while you do it once, then you can apply a short-cut key or button to run the macro whenever you like. Hope this helps. Pete On Oct 5, 6:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically insert rows with formula
Hi it's me again,
This is exactly my problem. I some times excell does copy format and formulas from the row above/below automatically. but other times it doesn't and I need to just insert a row and when I insert my values my formulas will automatically show it's outcome. I did the following step but still it's like it's turn off. please help me I Desperate :( step explain by HELP: Extend formats and formulas to additional rows By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows. Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows. You can turn this option off (or back on again) at any time: On the Tools menu, click Options. On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off. To turn automatic formatting back on again, select the Extend data range formats and formulas check box. "JP" wrote: Hello, You can protect the worksheet and still allow others to insert rows. If you are in Outlook 2003: 1. Unlock and unprotect all cells (Ctrl-A, then Ctrl-1, Protection tab, uncheck 'Locked' and 'Hidden') 2. Select the formula cells you want to protect, hit Ctrl-1, Protection tab, check 'Locked' and 'Hidden') 3. When protecting your worksheet, under 'Allow all users of this worksheet to", select 'Insert rows' HTH, JP On Oct 5, 1:54 pm, susy wrote: Hi my name is susy, I'm working on a finacial structure that should be used for entry of data by many person. I thought about locking the worksheet, but some else shoul be able to insert a row a keep working withou messing with my formulas. What should I do to automatically insert a row with the same formula at least with the same formats? susy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically Insert New Rows | Excel Discussion (Misc queries) | |||
How can I set excel to automatically insert rows | Excel Worksheet Functions | |||
automatically insert formula when new rows are inserted | Excel Worksheet Functions | |||
Automatically insert/delete rows to be x apart | Excel Discussion (Misc queries) | |||
how do you have rows automatically insert in excel? | Excel Worksheet Functions |