Convert Text to a Formula in Excel | The CRAZY method for tough Excel problems.

[@GoodlyChandeep]

Mark you're killing it! Awesome Production

[@ExcelOffTheGrid]

What can I say… I’ve learned from the best (i.e. You 😁)

[@BIGorilla]

Looking great Mark. That makes playing with conditions super easy, even the adjustments. Also the video looks fantastic. You’re getting even better at editing 🙏

[@ExcelOffTheGrid]

Thanks - I’ve been trying to up my editing game 😁

[@peltiertech1879]

Before Lambdas, we could define a Name, let's call it Eval, with a Refers To formula of =EVALUATE(G20), and in another cell simply use =Eval to display the result. But this means we can only use it to evaluate the formula in G20 (which is hard-coded in the Refers To formula), so it's not as flexible as the Lambda. Maybe we should call this Name EvalG20, to distinguish it from other Evals we may need to define.

[@ExcelOffTheGrid]

I find it crazy that LAMBDA now gives a new life to Excel 4 Macros. They should have been removed a long time ago. But they are still here and they still work.

[@tlee7028]

Brilliant !

[@ExcelOffTheGrid]

Thanks 😁

[@josh_excel]

Nice, I'm surprised there's not already a built in function like this and now I'm wondering what other VBA can be run inside of a LAMBDA Function

[@user12345654]

Great! But would it not work without converting it into volatile function by using now() within it?

[@ExcelOffTheGrid]

It doesn’t calculate consistently without it.

[@B1897forzajuve]

Could you please explain why you have to make that LAMBDA volatile?

[@ExcelOffTheGrid]

For the same reason INDIRECT needs to be volatile. The name of a of a cell reference is not in the calculation chain. So, needs to be volatile to ensure the calculation occurs when the inputs change.

[@IvanCortinas_ES]

A big applause for being so daring with the content. Thank you very much for telling in a tutorial: LAMBDA, EVALUATE and formulas.

[@ExcelOffTheGrid]

Thanks Ivan - it’s an interesting technique that might help in a few scenarios.

[@Bhavik_Khatri]

Very nice

[@ExcelOffTheGrid]

Thanks 😁

[@dharmmu]

But i think the same answer we can have with simple filter function, not required either lamda or vba😊

[@ExcelOffTheGrid]

It's just an example of the technique, I'm sure there are loads of other ways. But is there an there an easy way to create a FILTER function with an unknown number of conditions?

[@dharmmu]

@@ExcelOffTheGrid hmm..I can use this formula for the same answer =FILTER(G4:I12,(H4:H12=I17)*(I4:I12>I15)*(I4:I12

[@excelrobot]

I’m a sucker for a good Lambda formula! Cool trick for making it volatile. Loved the editing too, pro quality! What software do you use?

[@ExcelOffTheGrid]

Thanks Erik 😁 I use Camtasia for recording and editing.

[@montebont]

Very creative thinking but IMHO a solution looking for a problem.

[@ExcelOffTheGrid]

Well actually this was a solution I created for an actual problem - a different scenario to the video, but also relied on the user entering the comparison operator as text. So I know there are use cases. Probably not many, but definitely some.

[@simsong1911]

Brilliant! By using the "old" macro I had to save the file as macro enabled. Before your awesome trick I was creating text like '=1+1 and then doing search =, replace by =.

[@dkarthikr123]

Din work for me..

[@robertbachman9521]

This is a crazy good idea. I have been using VBA to do numerical analysis (say numerically integrating an arbitrary function over time). For the advanced methods the algorithm takes a bunch of values at different points in time over the domain (not at just one point). The algorithm is complicated but the same in all cases. Having to also code in the function as well makes it too specific. If I could somehow just put a text formula for the function into the UDF that would generalize the method it would be very much like how Matlab does it. I would love to have something like =Let(a,A1,b,A2,Start,A3,End,A4,integrate('a*t^2+b',a,b,Start,End,{"t"}). This is a simple case where you do not need numerical methods, but there lots of real world cases that have no general solutions. Now you have me thinking!

[@ExcelOffTheGrid]

Great stuff - glad I could get you thinking 🤔

[@houstonsam6163]

Outstanding. In the past I've used the advanced filter function and VBA worksheet change event calls to accomplish this sort of filtering; this approach will be much smoother and more nearly transparent for my clients who are unaware of the advanced filter or who struggle to understand it.

[@ExcelOffTheGrid]

Great news - that is a use case I hand not considered , but it should wok. 💡

[@MonkEBoy-ud6kj]

Can this help get around the formula length limit? I ask in reference to setting up possible longer emails that can be sent out from Excel using formulas…

[@ExcelOffTheGrid]

It doesn't change the formula length limit - it merely executes the formula. So any formula issues which exist before will continue to exist.

[@ziggle314]

Wow, it's very cool that you can build a function dynamically and execute it. Thanks!

[@ExcelOffTheGrid]

Glad you like it!

[@stefankirst3234]

Crazy indeed 😅 But very good to know when the day comes. Thank's Marc!

[@ExcelOffTheGrid]

Hopefully the day will never come, but when it does, you're ready.

[@raimundojs9547]

Wow... this is a whole new level... impressive! The closest thing I had ever seen about this was the use of Expression.Evaluate("1 + 1") in Power Query M language. Thank you for sharing!

[@ExcelOffTheGrid]

It's a similar concept to Expression.Evaluate, but the VBA version at least has exists for the last 30 years. Have you ever had need to use Expression.Evaluate in Power Query?

[@raimundojs9547]

@@ExcelOffTheGrid I have not. In fact, so far, I've come across with just one single video on KZbin with that particular function. "Access Analytic" channel: kzbin.info/www/bejne/iZq3YqeIoLZ_abcfeature=shared Check at 06:16. Thank you for your reply.

[@h.esther9400]

I don’t understand… we could create that filter condition by entering it directly, couldn’t we? Or is the idea that a user could input their own parameters into the table without knowing how to write formulas? This is quite cool and creative, though.

[@ExcelOffTheGrid]

Yes - that is the example in the video. You don't want users to re-write a formula each time you want to add/remove a criteria. If it's for quick analysis, it ceases to be quick. Therefore, in the example you can add/remove criteria by typing the text into a box.

[@h.esther9400]

@@ExcelOffTheGrid great, thanks for your response!

[@lvtutorials3039]

Madness, beautiful madness❤

[@ExcelOffTheGrid]

Thanks 😁

[@petermcnaughton82]

Ifs there any situation when the fxTextToFormulaUDF would return #VALUE! with your "1+1" example

[@ExcelOffTheGrid]

If you've not got VBA enabled in the workbook... I think that might be the error. Otherwise I can't re-create the issue.

[@petermcnaughton82]

@@ExcelOffTheGrid Doesn't help when I can't spell volatile. thanks for looking