distinguish formula result from keyboard entry

[Aris Gaaff]

Is there a SIMPLE way to distiguish numbers resulting from a calculated
formula from numbers just entered from the keyboard?

In a worksheet, the values in a column are calculated by formulas.
I now want to overrule some of the values (integers) by entering a
numerical value from the keyboard, while keeping the other values
calculated by formulas.

Of course I can choose the View formulas option as a preference/option,
but then all formulas are shown and I have to switch the option on and
off every time and it is far from transparant. Also, macros and added
columns will do the job, of course.

The workaround I use now is the following combination of Validation and
Conditional formatting:
1. choose "general" as the number format for all cells in the range
2. set the validation condition to =CELL("format";<cell reference>)="F0"
3. this forces the operator to change the cell format to "number, 0
decimal places" before any number can be entered
4. set the conditional formatting to Formula is: =CELL("format";<cell
reference>="F0" and choose any format, such as bold/red.
5. then every cell with a bold/red value indicates a keyboard entry,
whereas other values indicate an underlying formula.

Is there a more elegant way ?

 

[Bob Greenblatt]

Is there a SIMPLE way to distiguish numbers resulting from a calculated
formula from numbers just entered from the keyboard?

In a worksheet, the values in a column are calculated by formulas.
I now want to overrule some of the values (integers) by entering a
numerical value from the keyboard, while keeping the other values
calculated by formulas.

Of course I can choose the View formulas option as a preference/option,
but then all formulas are shown and I have to switch the option on and
off every time and it is far from transparant. Also, macros and added
columns will do the job, of course.

The workaround I use now is the following combination of Validation and
Conditional formatting:
1. choose "general" as the number format for all cells in the range
2. set the validation condition to =CELL("format";<cell reference>)="F0"
3. this forces the operator to change the cell format to "number, 0
decimal places" before any number can be entered
4. set the conditional formatting to Formula is: =CELL("format";<cell
reference>="F0" and choose any format, such as bold/red.
5. then every cell with a bold/red value indicates a keyboard entry,
whereas other values indicate an underlying formula.

Is there a more elegant way ?

If you just want to see where data was entered vs. data as the result of a
formula, try Edit-Go to-Speical and click formulas. This will select all
cells containing a formula. If you select6 a range first, then only those
cells within that range will be selected.

Once selected, you can format them differently. Of course the formatting
will "stick" if one or more are subsequently changed, but you can do this
again, or record a simple macro to do it.

 

[Aris Gaaff]

Is there a SIMPLE way to distiguish numbers resulting from a calculated
formula from numbers just entered from the keyboard?
[skipped]

If you just want to see where data was entered vs. data as the result of a
formula, try Edit-Go to-Speical and click formulas. This will select all
cells containing a formula. If you select6 a range first, then only those
cells within that range will be selected.

Once selected, you can format them differently. Of course the formatting
will "stick" if one or more are subsequently changed, but you can do this
again, or record a simple macro to do it.

Thank you !