concatenate result into a formula?

I have a concatenate function in cell B21 that yeilds "Average(sheetabc! B130:2000)"

Currently, I want to analyze that result in cell B24, but I have to copy B21 and paste special values into cell B24 and then add an equals sign.

My question: Is there any way to just have B24 be "=B21" so it will automatically do it and I can avoid the time consuming copy, paste values, add equals sign routine?

Thanks

Originally Posted by Pete_UK

There is an Evaluate function in VBA for doing this, but you can't call this directly from a worksheet cell. Instead, you need to set up a small User-Defined Function (UDF), like this:

Please Login or Register  to view this content.

and then you can use it like this:

=Eval(B21)

Hope this helps.

Pete

Thank you for the suggestion. I tried it but I get a "#NAME?" error as a result.

Could it be a conflict in names between the eval function http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx and the evaluate method http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx ? At this point, I might try some different names and make sure I understood the difference between the eval() function and the evaluate method.

the evaluate function already exists as excel4.0 and can be used in a named formula

insert name /define create name eg hdeuce
in refers to put
=evaluate(Sheet1!$b$21)
in b24 put =hdeuce

Originally Posted by Pete_UK

Well, there is only so much I can do from a distance and without seeing your workbook - perhaps you can attach your file (the FAQ describes how to).

Hope this helps.

Pete

Pete, as I was saving the dummy file it wouldn't work. I went back in to check soemthing and it worked. I have no idea why. I did hit enable macros every time. Either way it works. Thank you so much for the help.