Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function to test formula in a cell
Many thanks to all for the help provided. Just what I needed.
Fred Holmes |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |