How Do Ideas Form? I had a great one for testing protected/unprotected cells.

I was testing a budget file and my creative genius figured out a way to test for protected cells versus unprotected cells. This method works if you designate input cells as one or two colors. If you want to make sure that cells are locked or unlocked properly, here's what you should do.

Let's say you have a budget file you want to test - let's call it Budget file. Input cells are colored yellow or blue and should be left unprotected. The rest of the cells should be protected to prevent overwriting formulas. Now, create a new file called New. Starting from the upper left hand corner of the New file, type in the formula listed to the right. If your Budget file that you are testing starts in cell B1, then type the formula in cell B1 in the New file. Make sure the formula is not an absolute formula with $. Copy that formula across the columns and rows, just enough to cover all of the cells being used in the Budget file. So if your Budget file has formulas and input sections from cell B1 through AA200, then in the New file, copy the formula from B1 to AA200.

=IF(CELL("protect",'Budget'!B1)=1,"",0)

Use this formula if most of the cells are to be protected: 1 = protected; 0 = unprotected

Otherwise, use:

=IF(CELL("protect",'Budget'!B1)=0,"",1)

Next, go to Budget file and copy the format using the painter tool.

You want to copy the format of all relevant cells in the Budget file; in this example, we want to copy the format of cells B1 to AA200. Paste this format into the New file, starting in cell B1. After doing that, you might want to adjust the formatting for ease of reading. I used General format so I could spot all of the zeros. In the budget file that I was testing, all cells in either yellow or blue were input cells and thus should be unprotected to allow key entry.

Now, at a glance, I can spot which cells should be unprotected (the yellow or blue cells) and which ones should be protected (the white cells). Any zeros sitting in a white cell means that cell was left unprotected when it should be protected. Any yellow or blue cells that are blank will prevent anyone from making inputs because those cells are protected when they should be unprotected.

How did I come up with this brilliant idea? Well, it's typically from reading a lot, but this time it was due to exploration. I've always used CELL("type", cell ref) to discover whether the cell contains a blank, value or label. One day, I looked at the other options and noticed that there was a "protect" option. This exploration stayed in the back of my head until one day a problem came along looking for this solution.

You never know what will come up if you don't play or go exploring.