Is there a way to create a concatenated text cell back into a formula on a
different sheet (where the formula will refer to the cells in the new sheet,
not the original one) without having to manually change each cell in the new
sheet?

Let's say i have three worksheets in my file. I have a formula in Sheet 2
which concatenates two cell references (from lookup values from Sheet 1) and
turns them into text, but what i want to do is take this text string and make
this a formula that i paste into Sheet 3, with it automatically changing it
into a formula (the formula will refer to the cells in Sheet 3, not Sheet 2).
Unfortunately, because of other complexities in the file (it's a simulator
file), i can't just copy formulas from Sheet 1 to Sheet 3; that's why i have
to have the intermediate step of changing to text on Sheet 2.

For example in Sheet 2,
D96 value (lookup value): D26
F96 value (lookup value): D37

Cell H96 formula: ="="&D96&"*"&F96
which gives a result: =D26*D37
which i then want to paste in Sheet 3, but will use the values of D26/D37 in
Sheet 3 as oppossed to Sheet2

Thanks