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

=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