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]

benb wrote:
> 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

[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.
Franz Verga wrote:
> benb wrote:
> > 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.

"Franz Verga" wrote:

> benb wrote:
> > 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
>
>
>