Results 1 to 2 of 2

Thread: Interconnected columns for conversion

  1. #1
    Neophyte marco76's Avatar
    Join Date
    May 2021
    Posts
    1
    Articles
    0
    Excel Version
    2007

    Interconnected columns for conversion



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by marco76 View Post
    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.
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •