Howto: Protecting specifig Ranges for inserting Rows

Hi
I would like to protect specific Ranges of my Sheet. The users should be 
able to edit specific CELLS and additionally EDIT cells and INSERTnew Rows 
intos a specific predefined RANGE. But users should not be able to add Rows 
in the other parts of the Sheet. 

Do you know its possible? How to realize that?
0
5/20/2005 9:42:03 AM
excel 39879 articles. 2 followers. Follow

1 Replies
427 Views

Similar Articles

[PageSpeed] 41

Markus,

If you use a multi-cell array entered formula, Excel will prevent row
insertion (or column insertion) within that range.

For example, select A1:A10, type =ROW(), then press Ctrl-Shift-Enter.  Then
select a cell in, say, row 8 and try to insert a new row: Excel will
protest, and prevent the row insertion.

HTH,
Bernie
MS Excel MVP


"MarkusPoehler" <MarkusPoehler@discussions.microsoft.com> wrote in message
news:767DB676-7DBA-422C-B018-D6016AFAC5E0@microsoft.com...
> Hi
> I would like to protect specific Ranges of my Sheet. The users should be
> able to edit specific CELLS and additionally EDIT cells and INSERTnew Rows
> intos a specific predefined RANGE. But users should not be able to add
Rows
> in the other parts of the Sheet.
>
> Do you know its possible? How to realize that?


0
Bernie
5/20/2005 6:17:54 PM
Reply:

Similar Artilces:

Pictures won't insert.
Got a problem with bmp and pcx pictures not wanting to "insert" into my Publisher 2000 (the stand alone version). OS is Windows 98SE The alert window that comes up is: Publisher cannot convert this picture. This is either because Publisher doesn't recognize the format of the picture you want to add or there was an error loading a graphic converter. Publisher has graphics converters that enable it to recognize most picture formats. Some of these converters may be missing. To install converters for word processors and graphics, please run Setup again. For information on runni...

Row Hidden or height set to 0
The A1 row in my worksheet is not visible and I can't find a way to display it. Following help, I went to Edit/Go To/"A1" then Format/Row/Unhide or Height = 13 but it still isn't visible. Help! Hi click on the small box above the row numbers, left to the column headings to select your entire spreadsheet Now goto 'Format - Rows - Unhide' -- Regards Frank Kabel Frankfurt, Germany CS wrote: > The A1 row in my worksheet is not visible and I can't find > a way to display it. Following help, I went to Edit/Go > To/"A1" then Format/Row/Unhide or...

Nesting an If factor and using the range name
I am taking a course for advanced excel and I need help with a question> I am working on a spread sheet for real estate. I am supposed to set up a formula using NESector and CornerLot classifying them as shown below. 1, 2 or 3. Can any one help????? NESector CornerLot Classification Yes Yes 1 Yes No 2 No No 3 Assuming the NESector is in A1 and the first row of "Yes"s is A2 and B2; The formula needed in C1 is =IF(A2="Yes", IF(B2="Yes", 1, 2), 3) ...

Insert acrobat capability has disappeared on MS Publisher
MS Publisher 2003. I used to be able to insert a pdf file as an object. This capability has vanished from the "insert object" menu. This may be because my original Publisher has had an automatic update from version 11.5525.5606 to V 11.8212.8221. I have removed and reinstalled Publisher but the version level remains the same and the capability is still not there. My machine also has Acrobat V7 installeed but this has not changed. Have you tried Office Diagnostics in the Help menu? Right-click a toolbar, click customize, Options tab, see if Resetting menu and toolbar usage data...

Copy entire rows matching condition to a new sheet
Hi I want to copy entire rows matching a condition to a new sheet. The original sheet contains for example a column called impact with values between 1 and 20. Is there a possibility to automatically copy all rows with an impact > 16 to a new sheet called Priority 1, all with a value between 12 and 16 to a sheet priority 2, etc instead of copying the sheet manually, sort the data manually and delete those rows not meeting the criterium? Thanks very much This could be done by using the data>filter>autofilter. Record a macro while doing it so you can modify to do it again. -...

Inserting Timer
Hi Guys, How do I insert a Timer control to the form? It is not visible with the other control. I am using Access 2007. Regards, There is no such thing as a Timer control in Access. If you set a form's Timer Interval property to any value other than zero, then the form's On Timer event will run at the specified interval (in milliseconds). "Nad" <Nad@discussions.microsoft.com> wrote in message news:79107B1D-4A1F-4779-B16A-EB0ACB3D50D2@microsoft.com... > Hi Guys, > How do I insert a Timer control to the form? It is not visible with the > other control. >...

Open password protected ppt file using excel vba
Dim pptfile As Object On Error Resume Next Application.DisplayAlerts = false Set pptfile = CreateObject("powerpoint.application") pptfile.Visible = True Set pshow = pptfile.Presentations.Open(FileItem, Password:="") With pshow .Password = "test" ' ppt password .SaveAs FileItem .Close End With pptfile.Quit Application.DisplayAlerts = True End If How can i pass password from the vba code. If the file does not have a password then it catches error. Any...

Hiding rows and columns in XL 07
How do I get the top column row, which contains only the column letters, and the row number, which is the narrow column to the left of Column A, to disappear when I print? thanks everyone for your help. -mlenard Click File > Page Setup > Sheet tab Uncheck "Row and Column headings" > OK (Its probably checked) Success? Hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "mlenard" wrote: > How do I get the top column row, which contains only the column letters, and > the row num...

Blank rows in Address Book
When I click the Address Book button on the Outlook 2003 toolbard, I get a list of my Contacts. At the top, there are 6 blank rows before my first contact is displayed. I've tried to delete the rows by hitting the Del button while having one or more of these blank rows displayed. When I do this, a "Are you sure that you want to..." dialog box appears, so I click Yes but nothing is deleted. What be up with Dat? How do I get rid of the empty rows? Thanks, -m Did you try resetting the view? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 2...

Blank space gets inserted after text
For most columns, I am specifying a wrappable text format. Sometimes, after entering text in a cell, I get an extra blank line embedded at the end and I have to go back and manually close up the space. (So, if the text actually takes 2 lines, the row becomes 3 lines deep.) However, the problem is not consistent and I can't always reproduce it. Sometimes the height of the cell adjusts itself to the text just fine. I thought I may be inadvertently hitting the spacebar at the end of the last word, causing a wraparound, but that doesn't seem to be it. Any ideas? Hi Ros, Have you t...

Referencing a Row value from within a VLOOKUP
Hello group, I've worked with excel for many years, but I could use some help throwing this together. The background: I have two sheets within the same workbook. One sheet has check numbers and values that have been issued by the company. The other sheet has that same information, but only what has been reported back to us by the bank. I working on streamlining the comparison between the two sheets to more easily see what checks are outstanding (have not been cashed at the bank). Also, if the check has been cashed, I want to compare the value that the bank recorded with the v...

NOT protect Forms
Good Morning In a sheet I have a checkbox and a combo-box. I want to protect every cell in that sheet, make it "Read-Only", th only things I don't want to be protected are the two forms. So tha they keep on working, and I can continue selecting an item from th combo-box. Till now I can't manage that. By protecting the sheet, the two foms always are protected. I can't select them to be "no-protected". Please, Some knows the trick ? : -- skro ----------------------------------------------------------------------- skrol's Profile: http://www.excelforum.com...

Auto Sum unknown number of rows
Im using odbc to import data from a database. Imported is a list of products in column A and then various columns containing sales figures. Because of the import from the database, I do not know how many rows of products will appear, this week we sell 500 different products, next week 550. I need to add totals to the columns directly under the last product. I've tried creating the totals in an empty template and then inserting the imported data so the total figures move down and appear at the bottom. However the imported data moves the total cells to the right instead of down. Tried ...

Eliminating empty rows in Pivot table
Problem is that you can't simply filter out some data field because in some row fields they have values and at some they don't. So what I essentialy need is for those rows to exist when they contain data and to be erased when not containing data.I hope that it+s possible in excel 2007 Hi, Click on the immediate cell to the right of the Grant Total (for columns) cell, and press Auto Filter. This will get the auto filter drop down on the Grand Total column. Now filter out the Blanks. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishma...

Attachment inserts in middle of text
I am using Outlook 2000 and when I insert an attachment to a mail message, it places it in the middle of my text. I am a new employee to a small company and we don't have much in the way of tech support. When I have used Outlook in the past I have always been able to set it up to where the attachment goes at the bottom in a separate window. How do I set up my Outlook to place the attachments at the bottom in a separate window? Tools>Options>Mail Format. Change it to something other than "Rich Text" "ddk" <dkiefer@theballpark.biz> wrote in message...

Hiding rows
hope someone can help iv merged a whole bunch of documents into one spreadsheet that look something like this but on a much greater scale of course User Name JSoap MMouse DDuck Excel x x Word x PowerPoint x x Access x x i have about 12000 rows of these and about 250 columns which i have t take the information and input the data somewhere else. I dont wan...

cannot insert page break
I have RMB - Reset All Page Breaks. Then I select a ROW, RMB - Insert Page Break. They page break is not inserted. I also tried to add a vertical/column page break with no luck. I tried this from the pull down menus as well (Insert - Page Break). Any idea what could be wrong? Using Office Professional Edition 2003 Thanks, Sean Try checking your page setup. When I have this problem, it's usually because I've forgotten I had set the "fit to page" feature. This feature is not compatible with setting your own page breaks (which is understandable). Set the page scaling to ...

Insert two points horizontal
I need to insert a shaded area to show if my data is falling between two points. I have 12 months on the axis and vertical numbers reflecting amounts done. I want a bar that show if we met the lowest goal and another to show if we meet exceeded the highest amount. Average number would be 430 and it should fall inbetween 420 and 450. Is it possible to do this? You could use a technique like this to add several horizontal lines (min, max, target): http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ ...

Pivot Chart Row and Column Headers
The Formatting Toolbar on my Pivot Chart worksheet is dimmed. And in Tools>Options>View tab, the Window options are dimmed as well. How do I show the "Row & column headers" on my Pivot Chart? Thanks... A chart sheet doesn't have row and column headers. You could move the chart, so it's embedded on a worksheet. Right-click the chart, and click Location Click 'As Object In' and select a sheet iamnu wrote: > The Formatting Toolbar on my Pivot Chart worksheet is dimmed. And in > Tools>Options>View tab, the Window options are dim...

How do I create a range of the BIN in a Data Analysis Histogram?
Please call me ASAP about changing the range of the BIN from the histogram I created. ...

Insert row
Hi Can anyone help with this problem? I have a worksheet of 500 names. Columns A-P contain user inserted data. Column Q contains the formula (typical) =IF(COUNTIF(E5:P5,"")<12,"1","") Column R contains (typical) =B5 When I insert a row, the formula in Column R is copied OK. The formula in column Q is not copied. Can anyone say why this is happening? Thanks georgeporge ...

Inserting Autocad drawing
I am running publisher 2000. In the past I have been able to insert Autocad drawings into a publisher document. I upgraded to Autocad 2004 recently. Since that time I have not been able to insert autocad drawings. I even saved the autocad drawings back to an older version, still not luck. Any ideas? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Pete <pfrates@ci.healdsburg.ca.us>... > I am running publisher 2000. In the past I have been able > to insert Autocad drawings into a publisher document. I > upgraded to Autocad 2004 recently. S...

insert a blank row after data changes in a column
Hi, I have serveral very large spread sheets, and I need to insert a blank row when the data changes in a column for example \mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc I would want to insert a blank row after \mnt\snd\pvcs\ncs...

Protecting the print area
Is there a way to prevent readers from resetting the print area, or of keeping them from printing a range of cells on a worksheet? I'm new at this. You could use something like this in thisworkbook module, it will set the print area on sheet 1 before the sheet prints Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.PrintArea = "$A$1:$F$7" End Sub -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email...

row height not accounting for wrap text
I have a column formatted to wrap text but the text wrap is defeated because the row heights do not automatically adjust to fit the wrapped text. That is, the text gets clipped below the first line because the row height stays at one line high. Some rows contain a single line of text, but some have two lines, but they ALL get clipped to a single line because the row height does not adjust. This is very frustrating as I need to print these reports and no one will know what the heck the text says if they only see 1/2 of it! Any ideas? -- cwinters -------------------------------------...