Log in  \/ 
x
or
x
x
Register  \/ 
x

or

unanswered Convert all formula cell references to newly assigned names

  • james.r.walker@gmail.com
  • james.r.walker@gmail.com's Avatar Topic Author
  • Offline
More
3 months 4 weeks ago #1957 by james.r.walker@gmail.com
Convert all formula cell references to newly assigned names was created by james.r.walker@gmail.com
I inherited a spreadsheet to maintain.  The author, who no longer works here, did not use any names, so the cells are loaded with formulas like:

    =$C$69-$C$50-$C$51-$C$52-$C$53-$C$54-$Y75-$C$56-$C$57-$C$58-$C$59+$D$60+B$12

The first thing that I did was to start working on a copy of the spreadsheet.  Then:
    1) Created a new column with the name assigned to the cell in the row containing the cell. This allows viewing cell names at a glance.
    2) Created a new column with Excel FORMULATEXT function to see the value and formula at the same time.

I assigned names to the cells used in formulas.   When creating a new cell formula, EXCEL used its assigned name in formulas that now reference it.  However, old formulas referring to the newly named cell were not updated. 

Spent a long time searching for a way to update old formulas with newly assigned names.  Always search Kutools first and usually find what I want to do can be done with Kutools.   In this case,  there is a  Kutools "Convert formula References",  but it converts:  To absolute: To relative; To column absolute; and To row absolute.   It has no option to convert to names.

Finally, gave up searching for a solution and manually did all the Finds and Replaces.  As each Replace was done, the results were shown in the formulas column.  So, I knew the additional Replaces that still needed to be done.  It took a very long time because the cells reference types were not consistent in the formulas.

Question:  Is there a way to convert all formula cell references to newly assigned names with Excel or Kutools?

Suggestion:  If not already available, create a Kutools "Convert formula References" option to update all formula cell references to their names.

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun