Convert concatenated text into a formula?

[vanclute]

I have no clue if this can be done, and maybe it doesn't even need to be done if someone has a better solution to my proble. Here it is...

I'm using Excel with a stock analysis program called Investor RT. IRT will allow me to link via DDE to Excel so I can bring in values from my data provider directly into Excel for manipulation. Nifty! However, the format of the cel MUST be as follows for the DDE link to work:

=rt|symbol!data_column

Now what I want, is to, for example, make it do this:

=rt|$C3!data_column

So that I can fill down and have my formula automatically give me the data I want from my linked application on a ton of cels. However, Excel will not allow me to place a relative reference inside of the DDE formula. So...

1) Does anyone know how to do this?

or

2) I have a pseudo-solution. In another cel, I have:

=CONCATENATE("=rt|",B10,"!la")

where B10 is the cel with my ticker symbol. Now what this give me, is exactly what I want ultimately the formula to be. However the resulting cel of course just sees it as text, and not a formula. Is there any way to force a cel to behave as a formula regardless of what is generating the text?

Hopefully this makes sense... thanks a ton folks!

Jonathan

 

[just_jon]

Take a peek at the INDIRECT function, see if it makes sense to you. If not, post back & someone will pick up. Me, I'm back to work.

 

[vanclute]

Interesting... the indirect function is something I can imagine uses for, but I don't think that it can help me in this situation (though if you know how, please post! =). Thanks all the same... You or anyone else have any further suggestions or clever tricks?

Jonathan

 

[Aladin Akyurek]

=EVAL(CONCATENATE("=rt|",B10,"!la"))

which requires the morefunc add-in.

 

[vanclute]

WOW, sounds great but doesn't seem to work... I just get a #N/A error. Any idea why?

Thanks!

Jonathan

 

[vanclute]

Well I believe I foudn the problem... My formula has quotes in it already within the concatenation, and the EVAL function needs quotes around what it's EVALing. Any idea of a solution?

THanks!

Jonathan

 

[vanclute]

OK, I sortof solved it... the current formula I have is:

=EVAL("concatenate(G9,B9,H9)")

with G9 being '=rt| and H9 being !la and B9 being my ticker symbol.

And it returns what I'd expect it to... however all it does is SHOW me the formula, it does not actually execute or process it, which is what I need. To just display it I could've stuck with plain concatenate...

Any more suggestions?

Jonathan

 

[Aladin Akyurek]

Well I believe I foudn the problem... My formula has quotes in it already within the concatenation, and the EVAL function needs quotes around what it's EVALing. Any idea of a solution?

THanks!

Jonathan

I have no DDE to test. But try:

=EVAL(CHAR(34)&CONCATENATE("=rt|",B10,"!la")&CHAR(34))

 

[Yogi Anand]

OK, I sortof solved it... the current formula I have is:

=EVAL("concatenate(G9,B9,H9)")

with G9 being '=rt| and H9 being !la and B9 being my ticker symbol.

And it returns what I'd expect it to... however all it does is SHOW me the formula, it does not actually execute or process it, which is what I need. To just display it I could've stuck with plain concatenate...

Any more suggestions?

Jonathan

Hi Jonathan:

I don't think EVAL needs its argument to be enclosed in quotes. See the following simulation ...

Book5
	A	B	C	D	E
1	7	3		10
Sheet5

If I have misunderstood what you are working with -- my apologies!

 

[vanclute]

All I know for certain is, in this particular case, if I dont' place the concatenated formula in quotes within the EVAL statement, the EVAL fails with a #N/A.

Also, I tried your second suggestion Aladin but it also only displays the formula, rather than actually computing it, passing the value through DDE, and returning the stock price I'm looking for.

The formula =rt|CTAC!la entered into a cel, returns me the price of CTAC via DDE to my analysis program. However, any *construction* of that formula that appears correct to the viewer, but actually consists of other formulaic values, seems to fail.

Maybe this is a DDE limitation... if so, it's a rather horrendous one. =(

Anyone have any further suggestions?

Thanks everyone...

J