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

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

Answer 1

Top-ranking reply

Badunit

Level 7

28,084 points

Jan 21, 2023 7:57 AM in response to DaThiryPointBuck

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.

Reply

Answer 2

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.

Reply

Answer 3

Badunit

Level 7

28,084 points

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.

Reply

Answer 4

Jan 21, 2023 12:51 PM in response to Badunit

Baduinit,

I'm running v12.1. I replaced B with B2 as you suggested. That was the trick. Thank you

Reply

Answer 5

Badunit

Level 7

28,084 points

Jan 21, 2023 3:05 PM in response to DaThiryPointBuck

There were some bug fixes. Apple rarely tells what they were but it looks like that was one of them.

Reply