Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Visual Indicator if I've Overwritten a Cell with a Formula

I’m coming from Windows Excel where I could protect (lock) cells with formulas to prevent overwriting the formula.


In Numbers, I occasionally find myself doing exactly that, overwriting a cell’s formula.  I tried some recommendations from the forum, such as dragging a column/row out of the table, locking the new table and overlapping it with the old table.  While the technique works, in some cases it does not meet my needs.


I was curious if there is a way to use conditional formatting (or some other method) to highlight a cell if it does not contain a formula.  So, if I had a formula in a cell and I accidentally overwrote the formula, I could get an immediate visual indications I made a mistake, this cell is supposed to have a formula, but it's been overwritten.  I could then undo my mistake.


There are a number of conditional formatting rules, but I did not see one specifically for DOES or DOES NOT a contain formula.


Thank you

Windows, Windows 6

Posted on Jan 20, 2023 10:02 AM

Reply
Question marked as Best answer

I do not know. Here it is on my computer working correctly. You could try replacing the B with B2 for the one in cell C2 and see if that makes any difference. What version of Numbers are you using? I'm on 12.2.1.



Posted on Jan 21, 2023 7:57 AM

1 reply
5 replies

Jan 20, 2023 11:32 AM in response to DaThiryPointBuck

There are a few ways this might be done but not within the same cells. It is possible to get the cells to highlight if they do not contain a formula but it will require other cells or a column of cells elsewhere to do part of the work.


Here is a simple way put a flag next to cells without formulas. It assumes your formulas are in one column.


Create a column (column C) to the right of your column of formulas (column B). In C, next to each formula you are concerned about, put the formula

=IF(FORMULATEXT(B)≠"","")

The result will be the null string "" (which looks blank) if there is a formula or an error triangle if there is no formula.


If this doesn't suffice, tell us more about your table and post a screenshot if you can. A more complicated answer that highlights the exact cell(s) may require knowing if your formulas are in a single column or scattered around.

Jan 21, 2023 7:46 AM in response to Badunit

Badunit,


I want to thank you for taking the time to respond.


I think I understand the concept you explained. But, I do not get the result expected. I hope you will review the screenshot below and point me in the right direction.


I have formulas in column B that adds columns E & F. In column C is: =IF(FORMULATEXT(B)≠"","").


If understand correctly, I expect two see a null string ("") in C2 & C3 since both B3 & B3 contain a formula. In B4, I deleted the formula and see the error triangle as expected since there is no formula.


In my case, I receive the error triangle regardless if there's formula in any of the cells in column B, or if they are blank. The Data Format type are (A) Date & Time (B & C) Automatic (E & F) Currency. The Numbers' error message is: Argument 1 of FORMULATEXT is Invalid.


Again, thank you for your help.








Visual Indicator if I've Overwritten a Cell with a Formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.