Conditional Formatting - to highlight overwritten formulas
 

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

Conditional Formatting - to highlight overwritten formulas
 

See this:

http://groups.google.com/group/micro...cf859ecd?tvc=2

--
Biff
Microsoft Excel MVP


"JohnG" wrote in message
...
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

Conditional Formatting - to highlight overwritten formulas
 

Perhaps a simple, fast alternative ... Select the col/range, then press F5
Special check Constants OK. This selects all the non-formula cells within
the col/range in a flash, then just click to format at one go with the
desired fill color. Any worth? hit the YES below
--
Max
Singapore
---
"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

Conditional Formatting - to highlight overwritten formulas
 

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/200...e-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

Conditional Formatting - to highlight overwritten formulas
 

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
--
JohnG


"Max" wrote:

Perhaps a simple, fast alternative ... Select the col/range, then press F5
Special check Constants OK. This selects all the non-formula cells within
the col/range in a flash, then just click to format at one go with the
desired fill color. Any worth? hit the YES below
--
Max
Singapore
---
"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

Conditional Formatting - to highlight overwritten formulas
 

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/200...e-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

Conditional Formatting - to highlight overwritten formulas
 

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

Conditional Formatting - to highlight overwritten formulas
 

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

Conditional Formatting - to highlight overwritten formulas
 

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

Conditional Formatting - to highlight overwritten formulas
 

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

Conditional Formatting - to highlight overwritten formulas
 

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" wrote in message
...
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

Conditional Formatting - to highlight overwritten formulas
 

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" wrote in message
...
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

Conditional Formatting - to highlight overwritten formulas
 

On May 18, 10:24*pm, 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

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

Conditional Formatting - to highlight overwritten formulas
 

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 ....