Evaluate text string as a function

[benb]

I have a number of formulas used to pull data from another system into
Excel.
Three example formulas are:

=Systemx|Bid!GOOG
=Systemx|Ask!GOOG
=Systemx|Last!GOOG

The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the | is the
argument specifying what
data to pull into Excel. And you might recognize the argument after the
! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=Systemx|Ask!'A13', doesn't work.

I'm think there should be a way to construct the formula as a string
(e.g.
="Systemx|Ask!" & A13) and then force Excel to evaluate the string as a
function. As is, Excel will construct the string and then display it as
text
in the cell rather than evaluate it as a function. I know I can do a
work-around using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.

Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!

 

[Franz Verga]

I have a number of formulas used to pull data from another system into
Excel.
Three example formulas are:

=Systemx|Bid!GOOG
=Systemx|Ask!GOOG
=Systemx|Last!GOOG

The formula can be broken up into three arguments. Systemx is the
other system providing data to Excel via DDE links. After the | is the
argument specifying what
data to pull into Excel. And you might recognize the argument after
the ! as a
stock ticker. I want to replace the ticker with a cell reference so
that I
can change the value in the cell from GOOG to YHOO (for instance) and
have the formulas recalculate with the data from YHOO instead of GOOG.
If I try to just
replace the ticker with the cell reference, the resulting formula,
=Systemx|Ask!'A13', doesn't work.

I'm think there should be a way to construct the formula as a string
(e.g.
="Systemx|Ask!" & A13) and then force Excel to evaluate the string as
a function. As is, Excel will construct the string and then display
it as text
in the cell rather than evaluate it as a function. I know I can do a
work-around using VBA, but I'm hoping there is a solution without VBA,
perhaps a built in function.

Is anyone familiar with DDE links or otherwise know a way to fix my
problem?
Thanks for your help!

Maybe you can try using the INDIRECT function:

=INDIRECT("Systemx|Ask!" & A13)

or

="Systemx|Ask!"&INDIRECT("A13")

but be aware that using this function between two workbooks, they must be
both opened, because if the one addressed from the function is closed, then
the function will return the REF! error. So I don't know if this function
could work fine with DDE...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[xlcharlie]

Unfortunately, INDIRECT works much like using CONCATENATE. Excel displays
the text string rather than evaluating it as a function.

 

[benb]

Thanks Franz. Looks like you are monitoring both discussion boards as
well. I replied on the other board, but to reiterate, INDIRECT doesn't
seem to work. Excel displays the resulting text string rather than
evaluating the text string as a function.