CODEWITHSUNDEEP
vbaexcel
MennoSteenwijk
MennoSteenwijk

Reputation: 39

Replace cell reference in =formulatext output with variable name

I am working on a excel sheet where I need to show the formula that is used in another cell. I have 2 tables. Table one contains:

+-----------+-------+-------+------+
| Parameter | Short | Value | Unit |
|   Name    |       |       |      |
+-----------+-------+-------+------+
| Diameter  | D     | 50    | mm   |
+-----------+-------+-------+------+
| Wanddikte | T     | 5     | mm   |
+-----------+-------+-------+------+
| Lengte    | L     | 200   | mm   |
+-----------+-------+-------+------+

And the second table:

+----------------------+-------+-------------+------+-----------------+
| Name                 | Short | output      | Unit | Formula         |
+----------------------+-------+-------------+------+-----------------+
| Doorsnede oppervlakt | A1    | 1963,495408 | mm   | =0,25*PI()*C3^2 |
+----------------------+-------+-------------+------+-----------------+
| Binnendiameter       | ID    | 40          | mm   | =C3-2*C4        |
+----------------------+-------+-------------+------+-----------------+
| Verfoppervlakt       | Averf | 31415,92654 | mm2  | =PI()*C3*C5     |
+----------------------+-------+-------------+------+-----------------+

Now I want to change the last column of the second table. There you see the cell references: C3, C4 and C5.

Those refer to cells in the first table (Value column). But instead of showing C3 (value= 50 in table1) I want to show D (Short in table 1).

The last column in table 2 contains the excel formula: =FORMULATEXT(...) which refers to the output calculation in table 2.

How do I replace cell references with values from the Short column in the last column of the second table ?

Upvotes: 1

Views: 628

Answers (1)

QHarr
QHarr

Reputation: 84465

1) You could use named ranges. For example: C3 would be a named range called D. Then in your formula you would write =25*PI()*D^2 and you would have the FORMULATEXT as requested. C4 would be a named range called T and C5 a named range called L. To create the named range click on the cell you want to enter the name for e.g. C3 then go to the Name Box top left and enter then name e.g. D. See here: Named ranges

2) Consider having a helper column where you put the following: '=0,25*PI()*D^2 . Hide the column where you have the FORMULATEXT result and leave the helper column visible. The ' at the start means Excel will not try to evaluate the cell contents.

I think this might appear confusing if you use a simple letter such as D. This is not descriptive of what D actually is and can be confused as a partial cell reference.

Upvotes: 1

Related Questions