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: JohnG on 18 May 2010 22:30 Thanks Tom - I prefer not to stray into VBA/macros as less experienced users will need to maintain the spreadsheet model when I move on. If there is no simple solution then I will give this a miss -- JohnG "Tom Hutchins" wrote: > Here is one way. Add this little user-defined function to your workbook: > > Public Function HasRx(MyCell As Range) As Variant > HasRx = MyCell.HasFormula > End Function > > Select all of column A. Then add conditional formatting based on the > following formula: > =AND(LEN(A1)>0,HasRx(A1)=FALSE) > > Put the function code in a general VBA module in your workbook. If you are > new to user-defined functions (macros), this link to Jon Peltier's site may > be helpful: > http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/ > > Hope this helps, > > Hutch > > "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: Max on 18 May 2010 22:40 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: JohnG on 18 May 2010 22:56 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: Max on 19 May 2010 01:34 Its your processing/control requirements of course. But I'd still think that its better to "control" it properly via formulas protection rather than half measures where you face the difficulties of determining which formula cells have been overwritten*, and possibly the onerous task of re-instating formulas into all those overwritten cells for the next reporting round to users. Just some thoughts. *this was your original query -- Max Singapore --- "JohnG" wrote: > 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
From: Teethless mama on 19 May 2010 09:41
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 |