I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

If you overwrite a formula, it's gone.

Tyro

"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Thanks, but this is not what I had in mind. The formulas in question result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Thanks, but not exactly what I had in mind. I provide further details in my
response to cimjet. Thanks again.

"Tyro" wrote:

If you overwrite a formula, it's gone.

Tyro

"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Copy/paste this function to a module in your workbook.

Function IsFormula(cell)
IsFormula = cell.HasFormula
End Function

CFFormula is: =NOT(IsFormula(A1))


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 14:26:02 -0800, IvanM
wrote:

Thanks, but this is not what I had in mind. The formulas in question result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

This is a good, simple answer. I didn't consider that I don't really care
whether the cell contains a formula, but rather whether the value it holds
equals the value that the formula calculates. If someone overwrites the
formula with the same value the formula calcs, I don't really need the cell
to be shaded. Thanks

Biff has a cool solution, too, and it would be cool to see if it could also
work.

"Sandy Mann" wrote:

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

You're very welcome.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"BB Ivan" wrote in message
...
This is a good, simple answer. I didn't consider that I don't really care
whether the cell contains a formula, but rather whether the value it holds
equals the value that the formula calculates. If someone overwrites the
formula with the same value the formula calcs, I don't really need the
cell
to be shaded. Thanks

Biff has a cool solution, too, and it would be cool to see if it could
also
work.

"Sandy Mann" wrote:

Try something like this in Conditional Formatting:

=A5< <Your Formula

where <Your Formula is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"IvanM" wrote in message
...
Thanks, but this is not what I had in mind. The formulas in question
result
in values. We want to allow users to over-write a formula's value with
a
different value (manually input), as appropriate. I then want any cell
where
the formula's value has been over-written to be automatically
highlighted
(say with shading) so supervisors can see where manual intervention has
been
taken.

"Cimjet" wrote:

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select
Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet
"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Select all the cells prior to FormatCF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 15:39:01 -0800, BB Ivan
wrote:

This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks

Great, thanks so much.

"Gord Dibben" wrote:

Select all the cells prior to FormatCF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 15:39:01 -0800, BB Ivan
wrote:

This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

I find myself constantly using the "Format Painter" (icon that looks like a
paint brush.) to copy formats.

"BB Ivan" wrote in message
...
Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP

You're welcome!

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Great, thanks so much.

"T. Valko" wrote:

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto EditCopy
Now, select the cells where you want the formatting applied.
Goto Paste SpecialFormatsOK

--
Biff
Microsoft Excel MVP


"BB Ivan" wrote in message
...
Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order
to
apply it to a large range of cells I'll need to set up conditional
formatting
indivually for each cell in the range (since the formatting must know
what
address to plug into cell_ref). Can you think of any way I could
substitute
that in so I can copy the conditional formatting to other cells? I
don't
see
a function that can give me the address of the "current" cell without
being
fed reference infomation.

Thanks again!

"T. Valko" wrote:

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select InsertModule
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto FormatConditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn
RED.


--
Biff
Microsoft Excel MVP


"IvanM" wrote in message
...
I want users to be able to see where they have over-written a formula
with
text or a value (don't care which). I can't find a function that
determines
whether a cell contains a formula. Is there such a function? Or is
there
some other way to accomplish this?
thanks