Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Friday, 06 December 2019
  0 Replies
  1.7K Visits
0
Votes
Undo
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. 
    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.   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?

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






 
There are no replies made for this post yet.