Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I determine if a cell has a value (#) or a formula (X*Y)?
I would like to have the cell font color change (conditional format) to
denote whether there is a value (a simple number) or a formula in the cell (X*Y) where X*Y could be a reference to another cell or just a typed in =2*3. This will help users know when they should use some caution in changing an input when there is a formula inside the cell or just simply over ride when there is a simple dumb input. Thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I determine if a cell has a value (#) or a formula (X*Y)?
I found this in this link: http://www.j-walk.com/ss/excel/usertips/tip045.htm 1. Select Insert, Name, Define. 2. In the Define Name dialog box, enter the following in the 'Names in workbook' box: ContainsFormula 3. Then enter the following formula in the "Refers to" box: =GET.CELL(48,INDIRECT("rc",FALSE)) 4. Click Add, and then OK. 5. Select all the cells to which you want to apply the conditional formatting. 6. Select Format, Conditional Formatting 7. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box: =ContainsFormula 8. Click the Format button and select the type of formatting you want for the cells that contain a formula. 9. Click OK. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489076 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I determine if a cell has a value (#) or a formula (X*
Vito,
Thanks a lot. I have been looking for that trick for quite some time. Ken "Vito" wrote: I found this in this link: http://www.j-walk.com/ss/excel/usertips/tip045.htm 1. Select Insert, Name, Define. 2. In the Define Name dialog box, enter the following in the 'Names in workbook' box: ContainsFormula 3. Then enter the following formula in the "Refers to" box: =GET.CELL(48,INDIRECT("rc",FALSE)) 4. Click Add, and then OK. 5. Select all the cells to which you want to apply the conditional formatting. 6. Select Format, Conditional Formatting 7. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and then enter this formula in the adjacent box: =ContainsFormula 8. Click the Format button and select the type of formatting you want for the cells that contain a formula. 9. Click OK. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489076 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How do I set a cell value based on a formula in another cell? | Excel Discussion (Misc queries) | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
formula to determine if cell is merged? | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |