Formula Consistency Checking

[mikecroom]

I have created and am handing over a big spreadsheet.
Every so often I get an issue with formulae being inconsistent between rows:
eg row 1 of a table refers to row 1 of another table and verifies/reformats/recalculates it
then row2 does the same for row 2 etc
BUT - then you can get rogue rows where the rows do not match. Probably finger trouble but very hard to detect.

The Error Checking button does not help.
I was thinking of adding a column which checks the rows refererenced - but I don't even know how to extract the formula in one cell and process in another

Mike

 

[GlennUK]

Can you show a couple of the formulas as an example?

Are you happy to use VBA?

 

[mikecroom]

Very happy with VBA, long formulae ... anything

Just never addressed this issue before

 

[GlennUK]

If you compare the FormulaR1C1 property of the cells in a column, would they all be consistent? ( I'd have been able to see this myself if you'd given examples of formulas as I suggested )

 

[mikecroom]

Glenn

Thank you.

Sorry I didn't post the formuale concerned:
1) the ones I was looking at were very trival (and then there are some monsters)
2) I was eating my lunch at the same time

I never use R1C1 "stuff" but this exactly does the trick. So thank you again.

Looks like:

Function ShowRow(rngCell As Range)
   ShowRow = rngCell.FormulaR1C1
End Function

I just ensure that the whole column has the same R1C1 contents as you suggested.

Now, strictly I could check every column but I think it is an issue with some rows being mis-propagated

Mike
Mike

 

[GlennUK]

That's great!