0
Undo
Votes
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.
=$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.