Copying a formula from a text, how to get the values w/o F2?

[jdelrio]

I have a big spreadsheet (10k rows) that uses a formula that references values from other columns. To create the formula, I created a text and concatenated it to the values, then, brought that text to a new cell as paste values only. The result is the correct formula, but it is displaying the formula -not the result from it- until I go to each cell, press F2 and enter, then I get the value. Example:

a1 = 1
b1 = 1
c1 has the formula ="+a1+b1"
on d1 I pase values from c1.
Now d1 displays +a1+b1
On d1 I press f2 and enter,
Now d1 displays 2

How can I avoid the f2+enter and get the 2 directly?

thank you

 

[Zack Barresse]

Hello jdelrio, welcome to the board!

If you go to Tools | Options | Calculation (tab), is it set on Manual? Change it to Automatic to get it to change for you. Or press F9 manually everytime to calculate the sheet.

 

[hatman]

I would use the Indirect() function to build the formula directly:

in cell c1 rather than building the text string "+a1+b1", build the text string "a1" only, and in another cell, say d1, build the text string "b1", then in e1 write the formula =indirect(c1)+indirect(d1)... the indirect function redirects excel to the cell represented by each text string.

I hope this helps.

 

[hatman]

Hello jdelrio, welcome to the board!

If you go to Tools | Options | Calculation (tab), is it set on Manual? Change it to Automatic to get it to change for you. Or press F9 manually everytime to calculate the sheet.

I think his problem is that he is pasting a text string into a cell, and Excel does not recognize that it is also a formula until he uses F2 to force it... in that case F9 will not force Excel to make that realization.

 

[JONESY]

Hi

i'd like to help, but theres a few things a don't understand.

why are you copying and pasting to get the formula result

If you're looking for one formula to do everything I'm sure the board could help with just a little more info on what you want the concatenated formula to do

I guess your formula that references values from other columns is either a vlookup, Index(match) equivalent

L

 

[hatman]

Jonesy makes a good point: there may be a better way of doing this... my earlier suggestion makes some assumptions that may actually result in doing this the long way-round... perhaps with a little more background we can help you find something more elegant.

 

[Zack Barresse]

Ah. So then with the Morefunc add-in, you could use ..

=EVAL(C1)

 

[hatman]

Ah. So then with the Morefunc add-in, you could use ..

=EVAL(C1)

I haven't used that one myself, but it sounds like that would be a possible soultion here.

 

[jdelrio]

thank you for your replies. Indeed the F9 does not work because excel is thinking of it as text and yes, I am using vlookups. More info:
Have 2 worksheets:
Worksheet A has column that will be a lookup on worksheet B.
Worksheet B has 1000 values on col A that are grouped so I can look for them form WS-A.
On WS-A I created two columns with the following as example:
=MATCH(D66,pgmctrl!C:C,0) and =MATCH(D66,pgmctrl!C:C,1). Then I use the values from the previous to build my formula:
=+"=VLOOKUP(PROGRAMS!L$2,pgmctrl!$D"&I66&":$D"&J66&",1,FALSE)"

and this is the formula I am pasting, getting:
=VLOOKUP(PROGRAMS!L$2,pgmctrl!$D1393:$D1401,1,FALSE)

which is what I want...

 

[RalphA]

Oops!

Say, how do I delete this post? I used to see an X that allowed me to delete my post.