In the another display of passive aggressive behavior, I want to show you a trick you can use to prevent anyone from adding or deleting rows or columns by using a simple array formula.
I’m pretty sure I got this trick from Bob Umlas. Most Bob tricks, they are extremely cool, but I never seem to find situations where I can use them. But, maybe this trick will hit the spot for some of you.
.
.
Step 1: Highlight the rows where you don’t rows added or deleted.
Type =”"
Press Shift+Ctrl+Enter to apply the array.
.
.
Step 2: Highlight the columns where you don’t rows added or deleted.
Type =”"
Press Shift+Ctrl+Enter to apply the array.
.
.
Step 3: Hide the array formulas along with anything else you don’t want your users to see.
When your users try to add or delete a column or row in the protected area, they’ll get a face full of error message.
.
.
I think this would be useful if you didn’t want apply sheet protection but still wanted to prevent structural changes. Although – I can’t think of any reason why you would stay away from sheet protection.
What do you think?
RELATED STUFF
- Using Arrows to Edit a Named Range
- Outta My Way Formula Thingy!
- Zoom into Spreadsheet on Double-Click
- Find and Highlight Specific Values
- Choosing Quarters in Excel
- Creating Subtotals on Many Columns
- Creating a Frequency Distribution with a Pivot Table
- Lazy Alternate Shading
- Limiting Movement within Your Worksheet
- Filter on Selection in Excel
