How can I insert a text string of formula into the middle of another formula?

[jackso]

Hello, I have spent a few days on figuring out how but have no clue...

=A1&VLOOKUP(K1,AF$1:AH$45,3)&C1

where the VLOOKUP will return a text string of formula like: T$21&EVAL("E"&ROW())&U$23&AB1

how can I make it function as if it were like this: =A1&T$21&EVAL("E"&ROW())&U$23&AB1&C1

please help.........

 

[skywriter]

I'm not exactly sure what you are asking for, but maybe this will help you get some ideas.

Excel Workbook
	A	B	C	D	E
1	LookupValue			Joe	T$21&EVAL("E"&ROW())&U$23&AB1
2	Joe
3
4
5
6	=A1T$21&EVAL("E"&ROW())&U$23&AB1A2
7	LookupValueT$21&EVAL("E"&ROW())&U$23&AB1Joe
Sheet1

 

[jackso]

Hi skywriter,

Thank you for trying to help. I tried method in A6 before but this end up just having the text in the cell, what I want is to have it run like a formula.
Thanks!

 

[Andrew Poulsom]

Make sure that the cell is formatted as other than Text before you enter the formula.

 

[jackso]

Hi, all cells are formatted other than Text.
The formula in A6 (="=A1" & VLOOKUP(A2,D1:E1,2,0)& "A2") return this : =A1&T$21&EVAL("E"&ROW())&U$23&AB1&C1
and I want the result to run again so that I get the true value I want.

I now as a work around copy and paste special, and then click into each cell and press enter to make it work.

Thank you.

 

[Andrew Poulsom]

You can't do that, sorry.

 

[skywriter]

Here I got this to work on my computer with my data. Try my suggestion which is based off the cell references you had in your original post.

I got my version of this which looks at my data to work in cell A5. I think this is what you need.

="=" & A1&VLOOKUP(K1,AF$1:AH$45,3)&C1




Sheet1

*	A	B	C	D	E
1	LookupValue	*	Bill	Joe	T$21&EVAL("E"&ROW())&U$23&AB1
2	Joe	*	*	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	*	*	*	*	*
6	= LookupValueT$21&EVAL("E"&ROW())&U$23&AB1Bill	*	*	*	*
7	*	*	*	*	*
8	*	*	*	*	*
9	*	*	*	*	*
10	*	*	*	*	*
11	="=" & A1&VLOOKUP(K1,AF$1:AH$45,3)&C1	<<< Try this formula.	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A6 ="= "&A1&VLOOKUP(A2,D1:E1,2,0)&C1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4