Forum Discussion

Amanda Petersen's avatar
Amanda Petersen
Copper Contributor
Feb 14, 2018

Need formula to auto update cell reference

I am trying to compare Column A to Column B, so in C I did =A1=B1.   Where Column C says false, I delete the data from Column A (as B is the data I truly need).  When I have done this in the past...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
MVP

Hi Amanda,

 

In general here it shall be no difference between 2010 and 2016. Could you please attach Excel file as on your screenshot instead? 

Amanda Petersen's avatar
Amanda Petersen
Copper Contributor
Feb 14, 2018

See attached.  I deleted cell A3 and that shifted everything up, but I got the reference error.  Also, clicked in B5 and inserted to move the number 70 down and typically C6 would update to TRUE, but it didn't.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 14, 2018

    Amanda, perhaps your 2010 in manual calculation mode, and 2016 in automatic. You may check in File->Options->Formulas, or in ribbon

    at least for 2016

     

    • Amanda Petersen's avatar
      Amanda Petersen
      Copper Contributor
      Feb 15, 2018

      Unfortunately, that did not help. 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Feb 16, 2018

        Amanda, to my knowledge here is no difference between 2010 and 2016, all versions have the same behavior. The difference could be in manual and automatic modes, in first on Excel doesn't show the error until you recalculate (or close and open Excel). It looks like

        By the way, perhaps it's easier to copy and paste any empty cell on that place in A instead of deleting/inserting cells.

Resources