Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fred Holmes
 
Posts: n/a
Default IF Function to test formula in a cell

The IF() function is generally used to test the value in a cell. Is
there any way to get the IF() function (or some other function) to
test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact
true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when
a formula has been modified by some spreadsheet editing process and I
don't want the formula to change. Even "absolute" references seem to
change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1
Reference Style (because the formulae I'm interested in are literally,
character for character, identical in R1C1 reference style, and it's
easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a
question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes
  #2   Report Post  
Niek Otten
 
Posts: n/a
Default IF Function to test formula in a cell

With this little function you can "read" formulas

Function ShowFormula(a As Range) As String
ShowFormula = a.Formula
End Function

In your case, to get the R1C1 version, use a.FormulaR1C1 in the second line

To be able to use the function:

Open the VB Editor (ALT+F11)
InsertModule
Paste the function in the code window

You can use the function in a cell, like in =ShowFormula(R1C1) or in a
formula

--
Kind regards,

Niek Otten

"Fred Holmes" wrote in message
...
The IF() function is generally used to test the value in a cell. Is
there any way to get the IF() function (or some other function) to
test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact
true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when
a formula has been modified by some spreadsheet editing process and I
don't want the formula to change. Even "absolute" references seem to
change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1
Reference Style (because the formulae I'm interested in are literally,
character for character, identical in R1C1 reference style, and it's
easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a
question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes



  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default IF Function to test formula in a cell

On Sat, 12 Nov 2005 09:33:36 -0500, Fred Holmes wrote:

The IF() function is generally used to test the value in a cell. Is
there any way to get the IF() function (or some other function) to
test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact
true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when
a formula has been modified by some spreadsheet editing process and I
don't want the formula to change. Even "absolute" references seem to
change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1
Reference Style (because the formulae I'm interested in are literally,
character for character, identical in R1C1 reference style, and it's
easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a
question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes


You'll need a VBA function to return as a string the actual formula. You can
then use that function in your IF function.

To enter the VBA UDF, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

You can then use a formula of the type:

=IF(ShwFrm(A1) = "ur-formula"),"OK","Error")

==============================
Function ShwFrm(rg As Range) As String
ShwFrm = rg.Formula
End Function
===========================

or, if it is more convenient to return the formula string in RC notation, you
could use:

===========================
Function ShwFrm(rg As Range) As String
ShwFrm = rg.FormulaR1C1
End Function
==========================

You could also write a UDF to do the whole thing.

For example (not debugged):

=========================
Function ChkFrm(rg As Range, formula as String) As String
If formula = rg.FormulaR1C1 then 'or rg.Formula, depending
ChkFrm = "OK"
else
ChkFrm = "Error"
End If
End Function
========================

You could also use conditional formatting color those cells where the formula
did not agree with what it should be.

All kinds of solutions -- but you will require VBA for them.


--ron
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Fred Holmes
 
Posts: n/a
Default IF Function to test formula in a cell

Many thanks to all for the help provided. Just what I needed.

Fred Holmes


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
 
Posts: n/a
Default IF Function to test formula in a cell

glad to help


--
Don Guillett
SalesAid Software

"Fred Holmes" wrote in message
...
Many thanks to all for the help provided. Just what I needed.

Fred Holmes



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
adding a formula in a cell but when cell = 0 cell is blank Mike T Excel Worksheet Functions 5 May 31st 05 01:08 AM
Function syntax to compare cell contents ES Excel Worksheet Functions 2 May 18th 05 03:53 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 09:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"