Identify Cells that have Formulas

[doublej_jj]

I have a column of formulas. I need to identify if any have been overwritten with a hard coded number.

Idea #1 - In the next column, use an If statement (Don't know what if statement to use)
Idea #2 - Conditional formating (Really this would be just an extension of the first)

Input is appreciated.

Jeff

 

[vaskov17]

there is a "Show Formulas" button that would do that under the "Formulas" menu

 

[Expiry]

You can use the CTRL & ` (left of the 1 key) to toggle between showing formula and result. You can then easily see if anything has been overwritten.

 

[doublej_jj]

I don't want to reveal all formulas - More subtle, ie feedback to a user if they change something they shouldn't.

I know I can protect, I am not interested.

Jeff

 

[Expiry]

Why are you not interested in protecting it, if they shouldn't be allowed to change it?

In my experience, people don't adhere to subtle feedback.

Either let them do it, or stop them. No inbetween.

 

[Jonmo1]

Try this

Insert - Name - Define
Name it HasFormula
=GET.CELL(48,INDIRECT("RC",FALSE))

Then in conditional formatting you can use
=HasFormula

Excel Workbook
	A	B	C	D	E
1
2	PASS	FAIL	F1		5
3	FAIL	PASS	F1
4	PASS	FAIL	F1
5	PASS	FAIL	F2
6	PASS	PASS	F1
7	PASS	FAIL	F2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =HasFormula	Abc
B2	1. / Formula is =HasFormula	Abc
C2	1. / Formula is =HasFormula	Abc
D2	1. / Formula is =HasFormula	Abc
E2	1. / Formula is =HasFormula	Abc

</td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=60" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Namen verstehen</a></td></tr></table></td></tr></table>



Hope that helps.