I want to Hide some Settings right in plain view on the spreadsheet, but the person using the spreadsheet keeps inadvertently deleting those settings! In Episode #1442, Bill shows us one solution to keeping those settings from being deleted.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Learn Excel from MrExcel, Episode 1442 – Hide and Protect Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Here's a problem that I've been dealing with recently.
I have a report I produced for someone.
They have all their data over here, and then way off to the right-hand side, I have saved some settings, that way I can remember how we calculated this report.
And i just don't want anyone causing any problems with these settings, so one of the things I do is, I change the font color to white, and it looks like they're not there but, you can always come back later and see them by selecting the cells.
All right, everything is good but then the problem I have is, I was on a GoToMeeting and watching what's happening, and she's deleting these rows up here.
She doesn't realize she's deleting the rows.
She's just so fast at Excel – how she cuts some stuff here, Ctrl X, and pasted it down here, Ctrl V, and didn't need these anymore and deleted it.
You know, even at this point, we're already in trouble.
They've gotten rid my settings.
So let me undo, undo, all right.
Here's a good way.
I don't know if it's a good way.
Here's an interesting way to protect these settings.
I want to come here to the right or to the left of the settings, and I'm going to create an array formula.
It can just be, =2, Ctrl+Shift+Enter, and that creates one very boring formula out here.
The beautiful thing about that is, Excel will not let you delete part of an array, so if we come here and say, right click, Delete, you cannot change part of right now.
That's going to confuse the person, but it's going to slow them down enough and make them call me and say, “Hey, your spreadsheet won't let me delete.” That's right.
There's that settings out there.
Oh, yes.
Also the same thing if they try and cut, Ctrl X, and then paste.
You cannot change part of an array, all right.
So I know I should be hiding it somewhere else on a hidden sheet or something like that.
It was just a quick and dirty thing, but this a little extra array formula out here that is just simply equal something, Ctrl+Shift+Enter, will prevent anyone from deleting or cutting that section.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
I'm Bill Jelen. Here's a problem that I've been dealing with recently.
I have a report I produced for someone.
They have all their data over here, and then way off to the right-hand side, I have saved some settings, that way I can remember how we calculated this report.
And i just don't want anyone causing any problems with these settings, so one of the things I do is, I change the font color to white, and it looks like they're not there but, you can always come back later and see them by selecting the cells.
All right, everything is good but then the problem I have is, I was on a GoToMeeting and watching what's happening, and she's deleting these rows up here.
She doesn't realize she's deleting the rows.
She's just so fast at Excel – how she cuts some stuff here, Ctrl X, and pasted it down here, Ctrl V, and didn't need these anymore and deleted it.
You know, even at this point, we're already in trouble.
They've gotten rid my settings.
So let me undo, undo, all right.
Here's a good way.
I don't know if it's a good way.
Here's an interesting way to protect these settings.
I want to come here to the right or to the left of the settings, and I'm going to create an array formula.
It can just be, =2, Ctrl+Shift+Enter, and that creates one very boring formula out here.
The beautiful thing about that is, Excel will not let you delete part of an array, so if we come here and say, right click, Delete, you cannot change part of right now.
That's going to confuse the person, but it's going to slow them down enough and make them call me and say, “Hey, your spreadsheet won't let me delete.” That's right.
There's that settings out there.
Oh, yes.
Also the same thing if they try and cut, Ctrl X, and then paste.
You cannot change part of an array, all right.
So I know I should be hiding it somewhere else on a hidden sheet or something like that.
It was just a quick and dirty thing, but this a little extra array formula out here that is just simply equal something, Ctrl+Shift+Enter, will prevent anyone from deleting or cutting that section.
Hey, I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
