Jump to content


Photo

Is there a way to concatenate multiple cell formulas (instead of resulting values)

EVALUATE FORMULATEXT INDIRECT range names

  • Please log in to reply
4 replies to this topic

#1 bennyb

bennyb

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 25 January 2016 - 08:37 PM

Is there a way to concatenate multiple cell formulas (as opposed to the resulting cell values) and return the concatenated formulas as a text value to the calling cell?

 

Also, is there a way to convert the cell text to a formula too?

 

for example...........

 

if cell a1 contains the formula "=a2+a3" & cell b1 contains the formula "=b2+b3"

 

is there a way to put a formula in cell d1 that returns a cell text value of "'=a2+a3+b2+b3"

 

Then in cell e1 convert the text to the formula that belongs to e1 so that the returned value is =a2+a3+b2+b3?

 

I want to do this in excel2010 / win7 system.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 25 January 2016 - 09:28 PM

Hi bennyb,

 

The FORMULATEXT function will extract the formula from a cell and return it as text. For example =FORMULATEXT(A1) will show the formula that is in cell A1.

 

Your second requirement isn't quite as straightforward, but you should be able to achieve what you need by using the INDIRECT function. INDIRECT will convert text into cell references that will work in formulas, but it won't accept an entire formula.

 

You can learn all about INDIRECT in the video tutorial: Expert Skills Lesson 4-5 Use intersection range names and the INDIRECT function.

 

I've also attached an example workbook:

 

Attached File  INDIRECT.xlsx   9.59KB   151 downloads

INDIRECT2.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 bennyb

bennyb

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 29 January 2016 - 06:32 PM

Thanks......  Playing around with this seems to be what I am looking for.

 

Just as a bonus question.........

 

In VBA......  Is there a command to call a "cells" formula and store as a string variable?  Any thought on how the code would look?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 29 January 2016 - 07:38 PM

Hi bennyb,

 

We can't offer VBA support on these forums, but there is an undocumented way to do this without using VBA.

 

There is a 'secret' EVALUATE function that can only be accessed by creating a range name. If you're not sure how to do this, you can find an explanation in the video lesson: Expert Skills Lesson 4-2 Manually create single cell range names and named constants.

 

For example, to execute a formula that is stored as text in cell A1, you could define the following range name:

 

Evaluate.png

 

The EVALUATE function will not work if you try entering it as a cell formula. It can only be accessed by creating a range name in this way.

 

After creating the range name, you can extract the result with the formula: =EvaluateResult

 

Note that, even though this doesn't appear to be a macro, it will actually cause Excel to run a macro 'behind the scenes'. This means that it will only work within a macro-enabled workbook.

 

I have attached a working example:

 

Attached File  Evaluate.xlsm   9.37KB   125 downloads

Evaluate2.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 bennyb

bennyb

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 01 February 2016 - 05:05 PM

Thanks for the secret solution.  This is the best!







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users