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

 

[vanclute]

OK, on further investigation... it appears to be the pipe character ( | ) that causes EVAL to get confused and need the quotes. Any EVAL I do on cels that don't contain a pipe, compute fine without quotes.

Weird...

J

 

[vanclute]

OK, this is getting stranger and stranger...

In Cels I7, J7, and K7, I entered the numbers 1 2 and 3 respectively. Doing a =EVAL(CONCATENATE(I7,J7,K7)) returns what I woudl expect, which is "123". However if I change any of those cels to be a letter or any non-numeric character, the results are unpredictable, ranging from strings of 4 letters, to errors.

For example, in those cels, placing 4, b, and 3 results in a #VALUE error. Placing a, 2, 3 gives me "AZG" and putting A, B anc C in them returns #NAME?

I have no idea what's going on... anyone?

Jonathan

 

[Aladin Akyurek]

OK, on further investigation... it appears to be the pipe character ( | ) that causes EVAL to get confused and need the quotes. Any EVAL I do on cels that don't contain a pipe, compute fine without quotes.

Weird...

J

Does

=EVAL("=rt"&CHAR(124)&B10&"!"&CHAR(124)&"a")

evaluates to the intended result?

 

[vanclute]

Wow this is quite some monologue I'm having...

I think I understand now. In these later cases, the EVAL function is trying to do what it's designed to do, which is to execute whatever it evaluates, in this case "AZG" was the text I had placed in Cel A23 which is what "a23" returned. So this is somewhat correct.

However I still don't understand why the DDE function just won't evaluate...

Jonathan

 

[vanclute]

Aladin -

No, I get a #VALUE error on that too...

J

 

[vanclute]

WHAT THE HECK???????

OK, this works now!

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

That's it. DOes exactly what I want.

This makes absolutely NO sense...

But thanks a million everyone. If it suddenly STOPS working again I'll post some more.

Maybe it's tryign to tell me it's past my bed time.

J

 

[vanclute]

OK I have it figured out and it is definitely a DDE issue. If anyone knows how to get aroudn this, I would really love to hear it as it's my final block to maknig this work.

The function =CONCATENATE("=rt|",B9,"!la") can be combined with EVAL to pass the correct information through DDE and get the expected result, but only AFTER somewhere else in the worksheet I have done a manual entry of =RT|oiim!la to "jump start" the DDE connection. Once it's opened everything works as expected, but unfortunately this defeats the entire purpose of what I'm trying to do, which is not be required to hand-enter over 2000 cels worth of data.

So... I'm on the right track at least. Any DDE experts care to chime in here?

THanks!

J

 

[spurtniq]

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

I don't know if this will be of any help to you, but, I've been working
on the same problem.

The EASIEST solution I've found is to copy the concatenated formula
out to a text editor (like notepad) and then copy it back in.

Since the text editor only recognizes the text, all you get is the formula.

Then when it's pasted back into excel it's the formula instead of the
concatenation.

It's easier to try than to explain.

But, it works.

hth

 

[vanclute]

Thanks for the reply. Unfortunately I was looking for an automated way to have Excel do this, which I don't at this point remember if I ever solved.

For what you've tried though, I have an even easier solution. Just copy the formula, then in a new cel, do a "Paste Special" and tell Excel to paste the results and not the formula. I forget the exact language and I'm not at Excel right now, but if you try Paste Special I'm sure you'll see what I mean.

Thanks again,

J

 

[lucianot]

I know this topic is old, but...

I have the same exact problem. Has anyone fouond a solution yet??