Formula to check formula?

[frankT68]

Hi!


I have a table with many rows, columns and formulas.

I'd like to use a formula to check if the cells referenced in formulas, are in the same row.


For example, if I have a formula like this:


=IF(AND(ISBLANK(S3);ISBLANK(U3));"";IF(AND(S3>0;U3>0);U3/S3;"Check the price."))



I'd like to check this formula if all referenced cells (S3 and U3 in the above example) are in the same row (row N# 3). If they're not from the same row, I'd like to be warned.


For example, in the formula below, there is a reference to cell S5, but it should be S3.

=IF(AND(ISBLANK(S5);ISBLANK(U3));"";IF(AND(S5>0;U3>0);U3/S3;"Check the price."))

I'd like to get a warning, perhaps something like "Please check the formula for right references"


I use excel 365.

 

[Gerald Higgins]

Is it an option for you to simply copy the correct formula down as many rows as required, to force them to be the same ?

Alternatively, some versions of Excel have formula error checking, which highlight any formulas that don't appear to follow the same pattern as their neighbours.
Not sure if excel 365 has this.

 

[frankT68]

Is it an option for you to simply copy the correct formula down as many rows as required, to force them to be the same?

Unfortunately, this is not an option because the table is meant more as a form and similar things are "grouped" together with empty rows between "groups".

Alternatively, some versions of Excel have formula error checking, which highlights any formulas that don't appear to follow the same pattern as their neighbours.
Not sure if excel 365 has this.

I'm aware of this option and Excel 365 has it. It only works when the wrong formula appears in one cell or in multiple nonadjacent cells. If cells with the wrong formula are consecutive, then it does not.