how to use substitute function to output a formula instead of a text

**pentaxslr** · 11-16-2009, 06:04 AM

im trying to import external data from an automatic update source, to evaluate stock performances in a live market environment. the forumla for quoting a real time stock price is:

=PS|QUOTE!'####-HK,last'

where #### is the stock number.

i have in my column A the whole list of stock numbers, and want to place the according formulas in the B column. i tried using the substitute function like this:

=substitute("=PS|QUOTE!'####-HK,last'","####",A1)

where A1 is "0002"

however the function can only output a string of text instead of the actual result of the formula, i.e, =PS|QUOTE!'0002-HK,last'

any advice is appreciated.

**teylyn** · 11-16-2009, 06:14 AM

maybe

=INDIRECT(substitute("'PS|QUOTE'!'####-HK,last'","####",A1))

note: your single and double quotes did not quite match up. I put spaces into the syntax below for illustration, but you need to check if this is right for your formula

" ' sheet name ' ! ' ####-HK,last ' "

**pentaxslr** · 11-16-2009, 06:42 AM

thanks, but invalid reference

**teylyn** · 11-16-2009, 06:43 AM

Well, I don't know the syntax of that function or formula you're using. It's clearly not Excel, so it might help if you could either read up on its proper usage or share some background here.

**DonkeyOte** · 11-16-2009, 06:44 AM

I'm not sure INDIRECT will work here, INDIRECT is used to evaluate ranges rather than formulae, as such I suspect you will need to try & make use of old EVALUATE call via Names (or use VBA).

Let's say your formulae are in column B, select cell B1 and then create a name called EVAL

RefersTo: =EVALUATE("PS|QUOTE|'"&$A1&"&-HK,last'")

Then try

B1: =EVAL
and copy down

that may, may not work - given we don't have the same functionality at hand we can not test.