display referenced cell value without losing reference itself (F9 loses 'smartness')

[fmplatt530]

Hi I'm doing engineering calculations in excel and I want my work to show (using FORMULATEXT), but I want the formulatext to show values instead of referenced cell (show "=18*48+20*36" instead of "=C2*D2+C3*D3"). When I use F9 to do this, the reference is lost and I have to reset references when updating the values in the calculation.

I'm not an advanced user and am curious if this is possible. Thanks

[TMS]

Probably not what you want, but, in this instance, you could use

Please Login or Register  to view this content.

If you select the FORMULATEXT cell and press F9, you get

Please Login or Register  to view this content.

so you could manually edit the cell to put in the &s and quotes.

Other than that, I doubt you can do what you want. Happy to be proven wrong though.

[fmplatt530]

Thank you, this gets me a lot closer and gets the job done. Is there a way for the value displayed to show to two decimal points? The formula is showing 18 when I want it to show 18.00

[TMS]

You'd need to use TEXT(C2, "0.00")

[XOR LX]

Hi,

If the operators are only ever either +, -, * or / then:

=TEXTJOIN(MID(FORMULATEXT(D1),MODE.MULT(IF(ISNUMBER(MATCH(MID(FORMULATEXT(D1),SEQUENCE(50),1),{"*";"+";"-";"/"},{0,0})),SEQUENCE(50))),1),,N(INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(FORMULATEXT(D1),2,99),"/","+"),"-","+"),"*","+"),"+","</b><b>")&"</b></a>","//b"))))

Note that FILTERXML is not available in the online version of Excel.

Regards

[fmplatt530]

Thank you TMS

[XOR LX]

(Self-deleted)

[fmplatt530]

This worked except it replaced the * with + in a couple spots. These are not the exact numbers, but for example formula reads 18*48+20*122, but textjoin cell reads 18+48+20+122

[XOR LX]

You're correct. Ignore my last formula. The first version I posted is the right one to use.

Regards

[fmplatt530]

The first formula worked. Thanks again

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.