Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Evaluate text string as a function
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Evaluate text string as a function
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Evaluate text string as a function
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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Evaluate text string as a function
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up a string of text within a string of text | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |