Convert formula written as text string into a formula

[breadzeppelin]

Hi all, I hope you are safe and well...

This should be relatively easy, and I'm sure I'm missing a trick...

Ok - The reasons why I'm doing this would probably take a while to explain, but the high level gist of it is this:

I have a cell (e.g. cell D1) that contains a formula as text (itself generated by formula from a number of other text strings etc.) e.g.: ="=IF("&A1&"=6,"&B1&","&C1&")"

and say:
Cell A1 contains the text value of: $X1
Cell B1 contains the text value of: $Y1
Cell C1 contains the text value of: $Z1

Which displays the formula in D1 (as text): =IF($X1=6,$Y1,$Z1)

Now - what I want to do is in cell D20 get the actual value from the formula (stored as text) in cell: D1 i.e. what does the formula: =IF($X1=6,$Y1,$Z1) evaluate to?

I thought something like =INDIRECT(D1) would do the trick, but that doesn't seem to work... and I don't think Excel still has the evaluate function any more, but I could be wrong?

I can do this reasonably easily with VBA or a custom function, but ideally I want to avoid that if possible (basically so it doesn't require a manual step to execute, a change event or a custom function that may not work for another user).

Anyone got a great way to achieve this?

Thanks for the help!

Oz

 

[shaowu459]

1) select D20
2) formula---define a name
3) enter =CAL in D20

 

[breadzeppelin]

Wow! That is an awesome reply shaowu459!!

Thanks so much!

It worked perfectly, but ran it ran into an issue (displaying the #VALUE! error) when using named ranges in the string e.g.:

Formula:
=IF(D9="No",SUBSTITUTE(BT10,H9,J9),BT10)

Displays as text:
=Rate_From_Rate+[@VAR5]

(where: Rate_From_Rate is a named range and [@VAR5] is a table column in the table that I am entering your named range method i.e. =CAL into (e.g. in table column 1 and VAR5 is another column in that table. Hope that makes sense!? The formula:

=IF(D9="No",SUBSTITUTE(BT10,H9,J9),BT10)

is however located outside the table in a normal cell, so not sure if that's causing the issue?

Sorry for the inconvenience, and thanks again for the swift response! It's got me a lot closer!

Oz

 

[dougmarkham]

That's a neat trick,
Thanks for sharing!

 

[shaowu459]

it worked for me, please check whether the rows of Rate_From_Rate is equal to the rows of [@VAR5]

 

[breadzeppelin]

Hey shaowu459

The named range Rate_From_Rate is a named range on a different sheet (e.g. Sheet1) to where the table is located(e.g. cell A15) and the table where [@VAR5] is a column is located on a different row number (e.g. header of [@VAR5] is cell G55 of Sheet2.

Any way round this?

Thanks again for all your help - the visuals are brilliant and super helpful!

Oz

 

[shaowu459]

firstly, I'm not sure the details of you data, if you could upload some screenshots of your data that would be helpful for us to understand your problem.
secondly, i don't know the reason to creat a formula in text first and then calculate the text formula, may be there are other ways will do what you want. So, can you please upload your original data and explain in detail what you expect to get?

 

[breadzeppelin]

Thanks shaowu459,

Due to privacy concerns etc. I'm unable to upload screenshots or the file itself, however I was able to rework the file to better fit towards the example ypu gave and this now works perfectly!

Thanks again for all your help here - this is really appreciated!

Oz

 

[shaowu459]

Thanks shaowu459,

Due to privacy concerns etc. I'm unable to upload screenshots or the file itself, however I was able to rework the file to better fit towards the example ypu gave and this now works perfectly!

Thanks again for all your help here - this is really appreciated!

Oz

You are welcom Glad to know you have solved the problem.