Converting text string back into a formula

[Kevin c]

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

 

[RagDyer]

I don't understand *why* you're doing it, BUT ... you *can* do it.

This will work on the formula that you posted.
If done correctly, the cell should *not* match the formula bar.

With that formula in H96,
Right click in H96 and choose "Copy"
Right click again, and choose "Paste Special",
Click on "Values", then <OK>.

While H96 is *still* selected, click,
<Data> <Text To Columns> <Finish>

Now, that gives you a *working formula*.

Select the *entire* formula in the formula bar,
Right click in the selection, and choose "Copy",
*** Then hit <Enter> ***

Navigate to Sheet3,
Right click in the cell you wish to receive this formula,
And choose "Paste",
And you've got your copied formula using the cell references in Sheet3.