Convert formula written as text string into a formula

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
1) select D20
2) formula---define a name
3) enter =CAL in D20
11.gif
 

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

it worked for me, please check whether the rows of Rate_From_Rate is equal to the rows of [@VAR5]
1591867689557.png
 
Last edited:

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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 :giggle: Glad to know you have solved the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,923
Messages
5,621,622
Members
415,848
Latest member
LCC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top