How to print out excel formulas with names instead of references?

2
votes

Let's say I have an Excel table that looks like this:

 -----------------------------------------------
| Name          Formula/input   Units           |
|-----------------------------------------------|
| ∆t            4               seconds         |
| velocity      2.5             meters / sec    |
| acceleration  0.625           meters / sec^2  |
| mass          10              kilograms       |
| force         6.25            newtons         |
 -----------------------------------------------

Where B4 and B6 are formulas:

 -----------------------------------------------
| Name          Formula/input   Units           |
|-----------------------------------------------|
| ∆t            4               seconds         |
| velocity      2.5             meters / sec    |
| acceleration  =B3/B2          meters / sec^2  |
| mass          10              kilograms       |
| force         =B5*B4          newtons         |
 -----------------------------------------------

Is there an easy way, either through adding a formula in column D, or using VBA to generate a printable/human-readable formula that would replace all cell references from Column B with corresponding names in Column A? So that I would end up getting something that looked like:

 -----------------------------------------------------------------
| Name          Formula/input   Units           Equation          |
|-----------------------------------------------------------------|
| ∆t            4               seconds         ∆t                |
| velocity      2.5             meters / sec    velocity          |
| acceleration  =B3/B2          meters / sec^2  velocity/∆t       |
| mass          10              kilograms       mass              |
| force         =B5*B4          newtons         mass*acceleration |
 -----------------------------------------------------------------

Any suggestions or thoughts would be super helpful. I've got a spreadsheet with hundreds of formulas like this across multiple sheets that needs to be converted. Thanks!

1
excelexcel-formula
One of the solutions: create UDF that takes argument pointing to the target cell with formula, UDF gets .FormulaR1C1 property from target cell (smth. like =R3C2*R[3]C[1]), parses another cells links from it eg with RegExp, returns formula where links replaced with values got from the linked cells neighbours to the left. - omegastripes

1 Answers

0
votes

If you right click on the B3 and B2 columns there is an option to "Define Name". If you define a name for the cells you can substitute that name for the cell designation. You would be able to replace "B3" with "Velocity" and use that name instead of "B3" in formulas.