Interconnected columns for conversion

Good Afternoon.

I am (unsuccessfully) trying to create a sheet with the scope to convert geographic coordinates from two systems A and B A=B+X and B=A-X. I don't know from the beginning which coumun i am going to fill (sometimes A, sometimes B).

I have two columns ( A and B ) and I would like them to be blank, then when fill column B I would like column A to show B-X, and when I fill column B I would like column A to show B+X. A sort of circular reference. And to look blank until filled.

I am trying to do it with circular reference but it is not working properly, and I would like to avoid circular reference itself. Any idea (preferably not involving VBE, cz I dont master it)?

Thank you

marco76 said:

I have two columns ( A and B ) and I would like them to be blank, then when fill column B I would like column A to show B-X, and when I fill column B I would like column A to show B+X. A sort of circular reference. And to look blank until filled.

Click to expand...

You're not going to be able to avoid circular references if you want no VBA.
You can help stop the circular reference alerts by enabling iterative calculation in File|Options|Formulas and under Calculation options, tick the Enable iterative calculation and keep the Max Iterations to a low number.
Next remove all formulae from column E while you add formula to column B:
=IF(LEN(E5)=0,"",E5+249.45)
Then add formula in column E:
=IF(LEN(B5)=0,"",B5-249.45)

Next remove all formulae from column F while you add formula to column C:
=IF(LEN(F5)=0,"",F5+289.558)
Then add formula in column F:
=IF(LEN(C5)=0,"",C5-289.558)

Now when you overwrite a formula in any of those columns you'll get a result in the corresponding column, but that's the important thing; once you overwrite a formula you lose that formula permanently.
To put the formula back automatically when the contents of that cell are cleared you do need VBA, at which point you might as well let VBA do the calculations for you.