Formula as text in cell -> convert to formula result

**AHFoddeR** · 03-11-2013, 08:08 PM

Hey guys,

I've got a cell containing text (which looks like a formula).

Like this:

Please Login or Register  to view this content.

I want the result of that 'formula' to appear in a certain cell. Is this possible? One possible solution is so copy, paste values, then recalculate the cell. (Could be done with a macro/vba)

I'm creating a dashboard where a user can compare two types of data and see it on a graph. It has many different data sources with different frequencies (weekly,monthly) so the formula changes based on what types of data you are comparing.

Ideas?

Thanks for your help,
FoddeR

**dredwolf** · 03-11-2013, 11:51 PM

VBA solution:

Please Login or Register  to view this content.

OR, (Built in macro)
Highlight Cell B1
goto Formulas Tab,Define Name...MyEvalM...Change 'Refers to' to = EVAL(A1)
this will create a Named formula Called MyEvalM that will evaluate the TEXT formula in the cell to left of it into the answer...

Note-BOTH Solutions recquire Macros Allowed, AND the "=" sign removed from the start of the text, so your sample text should look like this:

Formula:

Please Login or Register  to view this content.

To adjust the Named Formula for a single cell, Highlight THAT cell, Then change the A1 to the cell That contains the formula text
(EDIT_
Or a lookup function that returns the cell that contains the TEXT formula

)

Hope this helps

Edit 2-
The Second option has been stopped being updated for Excel, so they may get rid of it, BUT, as far as I know, it works for all current versions

**AHFoddeR** · 03-12-2013, 12:25 AM

Hey Dred,

I get a syntax error with the VBA solution... Ideas? Did it work for you?

**dredwolf** · 03-12-2013, 12:37 AM

My bad...The VBA should be this:

Please Login or Register  to view this content.

Was a brain skip, so again, so sorry,

Edit, I think i was editing my words, and somehow put a comma where it did not belong, I truly apologize for the blunder !

**AHFoddeR** · 03-12-2013, 01:39 AM

Hey Dred,

All good about the error

Not getting any code errors, but now the function is returning #VALUE!

This is what I've done:

Copied code into module 1 within the sheet
Tried =MyEval(B15) with B15 containing both "COUNTIFS('INVOLVE - Registrations2'!B:B,41153)" and "=COUNTIFS('INVOLVE - Registrations2'!B:B,41153)"

Pretty new to VBA so what am I doing wrong?

**dredwolf** · 03-12-2013, 01:52 AM

Okay, 2 Points,
1) if the formula would normally return an error, It still will, It's just like running the formula normally
2a) if that's not the problem, time to see a sample workbook..I would need to SEE what's happening to figure out what is not working..
2b) did you try the 2nd solution ?

Okay, that's 3 points, but 2b relates to 2a in the fact that if second solution works, but first doesn't, we need to (again) see what IS happening

**dredwolf** · 03-12-2013, 02:00 AM

One other thing to try is to not reference the WHOLE column...Try $B1:$B$100000 instead( or whatever the expected highest row is+ a 10% oops factor works out to be

)