Solved

Sometimes Excel green triangle "unprotected formula" seems random.

Posted on 2014-02-28
12
5,886 Views
Last Modified: 2014-03-01
There must be a workbook level option that I don't know about.

In one workbook I get a green triangle when I enter the following.
A     B      C
1     2       =sum(a1:b1)

if I change C1 to say =sum(a1,b1) the green triangle goes away.

If I open a new workbook, and reenter the demo, neither formula gives a green triangle.

I have been using Excel for 10 years and Excel 2010 for many months -- most of my workbooks do not act like this.
0
Comment
Question by:rberke
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
A green triangle is from error checking - go to File, Options, Formulas to review options.

I can't say why you are seeing it in this one case without seeing the workbook but I wonder if you have a blank workbook with odd formatting in xlstart?
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 230 total points
Comment Utility
rberke,

That's not an error at all. The reason you are seeing the error checking marker is because C1 is unprotected.

Go to the Protection tab in Format Cells, and you will see the Lock checkbox of C1 is unchecked.

The green triangle is trying to warn you that sheet protection is not going to protect your formula in C1 because it's marked as unlocked.

If you are like me, don't like to see all those green triangles, do not unlock any cell that has formula in them.
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
Here is the workbook. Sheet1 is normal (no green triangle.)  Sheet2 is weird.
eeExcelDemo.xlsx
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
as noted above that cell is not marked as 'locked' meaning that the formula can be overwritten accidentally even if the sheet is protected.

normally Excel creates new sheets with all cells marked as protected, the idea being that you un-protect those that need data entry before setting the sheet to protected. In this case you seem to have an entire sheet that has been marked as 'unlocked'

to return it to excel default you can select the entire sheet and do format cells, protection and check 'locked'


However, on the assumption that you are seeing this behaviour for sheets in new workbooks then we must assume that something is overriding the default behaviour and I don't see anything wrong with the file. My first thought would be to check to see if there is a template in XlSTART folder which has been overwritten at some point with this version where cells on sheet2 have been marked as unlocked
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
I posted in a VERY simple workbook that demonstrates the problem on my computer.

Knowing if it occurs on other computers would be very helpful in troubleshooting.

Is there any expert who is willing to spend a minute to open it on their computer and see if it misbehaves there?  

To the people that are saying it is because a cell is not protected, I have this question:  BOTH worksheets in the SAME workbook are unprotected. Why does one sheet get a green triangle and the other doesn't?

And, since both worksheets are in the same workbook, it is impossible for the Formula options to be different.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 270 total points
Comment Utility
You are confusing the 'locked' checkbox under format cells, protection and marking the WORKSHEET as Protected.
The Cells on each sheet  are formatted differently as we keep telling you. that formatting difference becomes important WHEN the individual sheet is marked as protected.

The fact that the workbook is NOT PROTECTED is immaterial, the cells on one sheet are marked differently to the cells on the other so Excel is warning you that IF you PROTECT the weird sheet the cell which is NOT LOCKED and contains a FORMULA will NOT be PROTECTED.

I opened your workbook and the 'weird' sheet cells are all marked as 'not locked' whereas the normal sheet are all marked as locked. Also if I add a new worksheet they are marked as locked which is the correct default so its not a general problem with the workbook

If you follow what I said in previous answer you will see that I have opened the workbook and investigated both sheets and suggested a) a solution to the specific issue and b) a possible reason why it is happening to you and not the majority of excel users.

on the 'working sheet' select any cell, right click, format cells, protection and you will see a checkbox ticked for 'locked'. This means when you protect that worksheet that cell will be protected. This is normal behaviour for excel, by default all NEW worksheets are created with all cells marked as locked but it makes no difference UNTIL the individual worksheets are protected in the review tab,

Someone changed the weird sheet to be 'not locked' by accident or deliberately because on the weird sheet that checkbox is NOT ticked. so excel puts the green flag indicator there to warn you. IF you took a minute to right click the cell, format cells, protection and check the 'locked' box the flag will disappear.

The more difficult aspect is why this is happening and I suggested a possibility for that as well - ie: someone changed the format of the cells on the second sheet of the workbook and managed to save that to the default template in XLSTART

What more would you like?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
rberke,

Everyone is trying to help you out here. All you need to do is read the experts replies.
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
Thanks, now I understand !!!

I really thought I was doing exactly what you said, but I apologize because I now see I was wrong.

I made an honest mistake.  Your instructions were correct, and I had almost followed them exactly:

What you said                               "Go to the Protection tab in Format   Cells,"
What I did was "Home Tab > Cells Group > Format  > Protect Sheet".  

It never fails to amaze me when I use an excel feature every week for 10 years and still do not completely understand it. I thought I understood sheet protection really well, but I now realize that I have been misinterpreting the following for almost 10 years:

"Locking cells or hiding formulas has no effect until you protect the worksheet (Review tab, Changes group, Protect sheet button."
exact context
From the above text, most people (including me) would think that locking a cell on an unprotected worksheet would have no effect. Most people (including me) would be wrong  !!!!



Finally, I also understand the source of the initial problem. Client send me hundreds of excel spreadsheets, but yesterday was the first time I got one with so many green triangles.   I knew I could get rid of them by clearing all formats, but I wanted to understand exactly which formatting was causing the behavior. Now I do thanks to you folks.

So, I am closing this question and splitting the points to all the experts who have been so patient with me.
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
glad it got cleared up, I've been using excel for even longer and it still surprises me too.
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
You guys have done more than just clear up a small problem.  

I just realized, this green triangle can now serve a very valuable purpose that has practically nothing to do with "protection".   It allows me to easily see which cells have formulas.  In the past I have used Goto > Special Cells > Formulas but in the future I might just unlock all the cells in the sheet.  Maybe with  ctrl A alt HOEL, or mabe write a "Toggle green triangle" macro that does the same thing.

With my new knowledge, I have corrected one of regmigrant's statements as follows:

The Cells on each sheet have different protection formats. Those formatting differences usually become important WHEN the individual sheet is marked as protected, but the green triangle is a little backwards. The green triangle appears when you unlock a cell in an unprotected sheet, then goes away when you relock the cell.  You never have to protect the sheet or workbook for this effect to occur. In fact, once you protect the sheet, the effect goes away and the green triangles disappear entirely.
Thanks again.
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
although your statement is technically true it misses the point of what the green triangle is telling you and its interaction with protected sheets;
Excel has a series of background error checks that you can toggle on and off, formulas in protected cells is one of them. Its purpose is to help you develop the sheet by flagging a potential error (you can choose which ones it flags under options, formulas) ; when you protect the sheet background error checking is disabled but the errors still remain. so the green flag disappear but the cell is still unlocked and can be overwritten - which you normally don't want in a protected sheet.

If you don't generally use Sheet protection to prevent accidental overwrite of formulas then use it in the way you describe, especially if you work a lot with Other Peoples Spreadsheets because I can appreciate that understanding their structure and finding formulas is time consuming; however the tab formulas, formula auditing  has some helpful tools including the ability to toggle formula display, modify error checking and trace formula cells)

the protect sheet functionality is intended for situations where a spreadsheet is being used by non-technical users who will put some data into specific cells but expect the formulas, charts and everything else to be done for them.

In general a sheet will have fewer data entry cells than formulas so excel defaults all cells to 'locked' meaning that when you protect the sheet the cell will not be available for editing. to enable data entry on a protected sheet you must format the appropriate cells to be 'unlocked' or no one can enter any data. The mechanism for telling Excel which cells are to be 'locked' when the sheet is protected is under the 'format cells, protection tab, locked check box'.

but this creates a problem during development because its easy to make an error and unlock a cell then put a formula in it as you work, then when the sheet is protected this formula can be overwritten even though its supposed to stay there. Excel flags this POTENTIAL error with a green flag in an unprotected sheet - it only appears when the cell is unlocked and there is a formula present.

However once you protect the sheet Excel assumes you are ready to deliver it to an end user for data entry so there is no point in flagging errors any more (users would be asking what it means all the time and they can't fix errors on locked cells) so (all) background error checking is disabled when the sheet is protected.
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
<<If you don't generally use Sheet protection to prevent accidental overwrite of formulas then use it in the way you describe,>>

You are exactly correct, I only use sheet protection for end user data entry sheets - which are pretty infrequent.

And thanks for your kind efforts to explain what happens when a spreadsheet is PROTECTED.  But I must apologize, for I should have made it clear that I am an experienced developer that has already done lots of work with protected sheets.

Nonetheless, perhaps your excellent comments will be useful to other researchers that might read this post.

The 2 new things of value that I learned today are 100% related to UNPROTECTED sheets, so all of today's discussion about what happens when a sheet is protected is irrelevant in that context.

#1.  I learned that locking a cell DOES have some effect on unprotected sheets.  Previously I had believed otherwise based entirely upon misplaced faith in the Microsoft help files.  

#2.  I learned that ctrl A alt HOEL is a pretty good way to highlight all cells which have formulas.  Then repeating that key sequence relocks the cells. (I liked that idea so much that I have assigned it to a macro.)

Of course, for end user sheets, we must be a little careful about using ctrl A alt HOEL. Its easily handled with a single line of code in the associated workbook_open  or perhaps close routine.  
[myLockedCells].locked = true.

There is no need for further discussion, and keep up the good work at experts exchange.

Bob

rberke
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now