Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Circular Reference Level of Hell (xl2000)

    I have been tasked with modifying an existing cell function to add an argument which when added induces a circular reference.

    This is the existing formula:
    =IF(OR(O154<>0,R154>0,S154<0,AND(P154<>0,P154<>P15 3),AND(P154<>0,P155=0),AND(H154>0,Q154=0)),1,0)

    The piece that causes the circ ref when inserted is Z154<70/6

    For example:
    =IF(Z154<70/6,1,IF(OR(O154<>0,R154>0,S154<0,AND(P154<>0,P154<> P153),AND(P154<>0,P155=0),AND(H154>0,Q154=0)),1,0) )

    If iteration is checked with a maximum change value of 0.001 and iteration value of 2 (or more) there are sufficient changes to the totals on the spreadsheet of over 4400 rows of data as to invalidate the information.

    I ran a procedure to check the value of each cell in column Z for a value of <70/6 and if true then make the cell in column W = 1. This the desired result but this overwrote the formula in the cell causing even worse damage to the sheet.

    Is there a way (in my infinite ignorance) to run a VBA procedure and send the result to a cell WITHOUT changing the existing formula? Or is that just a dream?

    I am losing hair rapidly (that which isn

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Circular Reference Level of Hell (xl2000)

    No, a cell can contain a formula or a constant, but not both. If you put the result of the calculation in the cell, it will replace whatever was there before.

    Do you know why you get the circular reference?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Circular Reference Level of Hell (xl2000)

    The argument Z154<70/6 is where the circ ref stems from. A formula in the Z column cells refer to W column cells. I had looked at the Excel VBA help, searched the KB at Microsoft, looked through sites by Walkenbach and Pearson, reviewed several XL books and could not find a workaround to this situation. I appreciate your response and your time in reviewing my question.

    I talked over the situation with my team leader and showed him the results of the various ways of trying to implement the argument in question and a different method of achieving the desired result was attained by modifying a formula in another cell.

Posting Permissions

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