Checking that the formula is correct and not just the result

[lynxbci]

Hi,
I have a cell with a formula syntax preceeded by an ' to show the formula
So for example i may have in cell A1 '=max(e13:e300)

The user is required to enter a formula into a cell B1.

It is a training spreadsheet and i want to compare the result of the formula, which is fine, but would also like to make sure they have used the correct formula syntax to get the result.

I can not use = as one is the formula and 1 is the result of the formula.

Any ideas?

Thanks as ever

 

[Sektor]

Activate cell with apostrophe and run procedure:

[COLOR="Blue"]Sub[/COLOR] CheckFormuala()
    
    [COLOR="Blue"]If[/COLOR] Evaluate(ActiveCell.Text) = ActiveCell.Offset(0, 1) [COLOR="Blue"]Then[/COLOR]
        MsgBox "Correct"
    [COLOR="Blue"]Else[/COLOR]
        MsgBox "Incorrect"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[GlennUK]

When you say that you want to check the formula syntax, do you mean that the formula will be exactly the same as the entry in cell A1?

 

[lynxbci]

Yes i want to check they have entered the 100% correct syntax
Ideally with a simple check not a function

Thanks

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] CheckFormuala()
    
    [COLOR="Blue"]If[/COLOR] ActiveCell.Text = ActiveCell.Offset(0, 1)[COLOR="Red"].Text [/COLOR][COLOR="Blue"]Then[/COLOR]
        MsgBox "Correct"
    [COLOR="Blue"]Else[/COLOR]
        MsgBox "Incorrect"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[GlennUK]

Select cell C1, and create a name ( Insert/Name/Define or Formulas/Defined Names/Define name depending on Excel version ), of name, say, FormulaCellToLeft with a definition of:
=GET.CELL(6,Sheet1!B1)
( adjusting the sheet name to suit ... NOTE the cell ref is B1, i.e. one cell to the left of current cell )
Press OK to save the name.

In cell C1 type:
=FormulaCellToLeft=A1
to check the formula in cell B1 against the text contents of cell A1.

 

[lynxbci]

Thanks for that, exactly what i wanted

On we go