Conditional Formatting - to highlight overwritten formulas
in [Functions]
|
Prev: Help with data points in line graph in Chart function in Excel.
Next: changing worksheet reference name when copying down cells
From: Rick Rothstein on 19 May 2010 23:08 Just out of curiosity, why not create the named range HasNoFormula and then use... =NOT(GET.CELL(48,$A2)) in the Refers to field instead? That way, the OP could use a more straightforward looking =HasNoFormula in the conditional formatting dialog. -- Rick (MVP - Excel) "Teethless mama" <Teethlessmama(a)discussions.microsoft.com> wrote in message news:17B2126F-8AD3-4911-A8BE-90717B52CEDA(a)microsoft.com... > Assuming your formula in A2:A10 > > Create a define name range call HasFormula, in the Refers to: > =GET.CELL(48,$A2) > > Select A2:A10 > Conditional Formatting: =NOT(HasFormula) > format any color you like > > > > "JohnG" wrote: > >> A number of people can access and update a spreadsheet that I designed >> for >> them. The sheet contains a column with lookup formulas. I need to be >> able to >> quickly scan down the column and see cells where the formulas have been >> overwritten with values/text. Is there a way to set this up with >> conditional >> formatting. >> My first thought was to use the TYPE function but it does not appear to >> be >> able to identify when the contents of a cell is derived from a formula as >> opposed to a simple text string or number. >> -- >> JohnG
From: Steve Dunn on 20 May 2010 10:07 That's a different request, and might be better handled by Data Validation than Conditional Formatting. If you can't define what may be put in the cell, just leave Any Value selected in Data Validation, and use an Input Message as your warning when they select the cell. HTH Steve D. "JohnG" <JohnG(a)discussions.microsoft.com> wrote in message news:3B8EAAC7-9A89-43BD-9A0F-410B9575CCF0(a)microsoft.com... > Thanks again Max. There are valid reasons for contributors to overwrite > these formulae where they have better information than the default result > from the lookup formula. My wish is to just have one extra prompt that > reminds them they need to be very sure of what they are doing when they > overwrite the formula > -- > JohnG > > > "Max" wrote: > >> well, I'd suggest that you just protect all the formula cells then, do >> not >> allow any overwriting. You could always ask users who "dispute" the >> formula >> returns to indicate their own values in an adjacent col in their >> submissions >> to you, for example. >> -- >> Max >> Singapore >> --- >> "JohnG" wrote: >> > Thanks Max - I am looking for an automatic solution as I also want the >> > less >> > experienced contributors to the spreadsheet to realise that they have >> > overwritten the default formula. I also prefer not to use VBA or >> > macros in >> > the solution as someone less experienced will need to maintain the >> > spreadsheet when I move on
From: Chechu on 21 May 2010 08:50 On May 18, 10:24 pm, JohnG <Jo...(a)discussions.microsoft.com> wrote: > A number of people can access and update a spreadsheet that I designed for > them. The sheet contains a column with lookup formulas. I need to be able to > quickly scan down the column and see cells where the formulas have been > overwritten with values/text. Is there a way to set this up with conditional > formatting. > My first thought was to use the TYPE function but it does not appear to be > able to identify when the contents of a cell is derived from a formula as > opposed to a simple text string or number. > -- > JohnG Hi John, I had the same situation last week: A template with a default formula, but due to business reasons the user can overwrite it. Initially I tried to use conditional format, so if cell has not formula = other color. However, I found that many users overwrite the formula with just another formula! So be careful with this approach What I did to solve it is I added the conditional format as Formula IS NOT EQUAL TO, then put there the original formula, and turned blue if not the result of my formula. Regards, C
From: Max on 21 May 2010 19:33
Savvy users may also use a simple link formula to replace your existing formula (that makes it indistinguishable from overwriting formulas with a constant), careless users may also damage formulas in cells they are not supposed to touch, the list goes on .... |