0

I have created an expression using "&" to concatenate different cells, I created this:

=INDEX((Data_sheet!A1:M20440;Data_sheet!A20441:M40880;Data_sheet!A40881:M61320);20;5;D2)

Now, I have the expression that is a formula, but it is not evaluated as a formula when I paste it, I can see the whole expression in the cell, but I want to get is the value that is behind evaluating that formula. I want to know how to make it to evaluate as a formula (because I will have thousands of this expressions)

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jack Green
  • 141
  • 2
  • 12
  • How do you create all these formulas? using excel drag fill? – Gowtham Shiva Apr 05 '17 at 07:16
  • The issue is that the expression is not evaluated as a formula. What a get is a cell with the expression above, but to evaluate it I need to push "return" in each one of the expressions. What I want is to paste the expressioan and excel to evaluate it as a formula. – Jack Green Apr 05 '17 at 07:52
  • Possible duplicate of [How to turn a string formula into a "real" formula](http://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula) – Michael Apr 05 '17 at 07:53

2 Answers2

2

My guess is that you would have created the INDEX formula in excel using multiple other formulas.

Once you have created the formulas, copy all the formulas and paste it in a txt file. Then, copy all the formulas from txt file and paste it again in the excel where you want the formulas to perform. This is the simplest way of doing this without creating any macros or additional formulas

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
1

See this question for solutions:

How to turn a string formula into a "real" formula

In particular, also note the answer about Excel's hidden EVALUATE function, that can only be accessed after defining a name to use it.

https://www.vertex42.com/ExcelArticles/evaluate-function.html

Community
  • 1
  • 1
Michael
  • 4,563
  • 2
  • 11
  • 25
  • I must continue checking, becuase the version of my Excel is different, But, there must be an easier way. – Jack Green Apr 05 '17 at 08:12
  • There are numerous questions about this exact issue, where you have constructed a formula/expression and then want to evaluate it. Unfortunately there aren't any other methods. It's either VBA or the hidden EVALUATE function. – Michael Apr 05 '17 at 08:15
  • Thank you for the answer. I think I will use the method of copy-paste to a notepad file and after from a notepad file to an Excel sheet – Jack Green Apr 05 '17 at 08:37