Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use text value inside a formula
I want to grab the text contents of a cell and use that in a formula
in another cell. So, say, for example, A1 contains TEST. Can I then put TEST in a formula automatically in another cell formula, so that the formula will essentially be: =VLOOKUP(A5,[TEST.xls]Sheet1!$A4:$H203,2,FALSE)... I have stock tickers in a cell and filenames named after each ticker and want to do lookups into each ticker's file. The ticker symbols change every day. Any advice appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use text value inside a formula
Hi Gary,
I want to grab the text contents of a cell and use that in a formula in another cell. So, say, for example, A1 contains TEST. Can I then put TEST in a formula automatically in another cell formula, so that the formula will essentially be: =VLOOKUP(A5,[TEST.xls]Sheet1!$A4:$H203,2,FALSE)... I have stock tickers in a cell and filenames named after each ticker and want to do lookups into each ticker's file. The ticker symbols change every day. Any advice appreciated. You want to take a look at the INDIRECT worksheet function, which does what you want, with one annoying restriction: the files pointed to must be open in Excel for this to work. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use text value inside a formula
Thanks. I've read up on INDIRECT and wrote:
=("["&(INDIRECT("A5"&".xls]Sheet1!D1"))) It gives me a #REF! error. I believe I've written this correctly, but obviously I haven't. Do I have a syntax problem? What am I not understanding? thanks for any help. Gary Jan Karel Pieterse wrote in message ... Hi Gary, I want to grab the text contents of a cell and use that in a formula in another cell. So, say, for example, A1 contains TEST. Can I then put TEST in a formula automatically in another cell formula, so that the formula will essentially be: =VLOOKUP(A5,[TEST.xls]Sheet1!$A4:$H203,2,FALSE)... I have stock tickers in a cell and filenames named after each ticker and want to do lookups into each ticker's file. The ticker symbols change every day. Any advice appreciated. You want to take a look at the INDIRECT worksheet function, which does what you want, with one annoying restriction: the files pointed to must be open in Excel for this to work. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use text value inside a formula
Hi Gary,
Thanks. I've read up on INDIRECT and wrote: =("["&(INDIRECT("A5"&".xls]Sheet1!D1"))) It gives me a #REF! error. I believe I've written this correctly, but obviously I haven't. Do I have a syntax problem? What am I not understanding? The safest method to create an external link using INDIRECT is: First hardcode a direct reference. Then construct a string formula so that the result is identical to that reference. Finally wrap that string expression with the INDIRECT function. Yours is wrong, it should read something like: =INDIRECT("'["& A5 & ".xls]Sheet1'!D1") Note that sometimes apostrophs are needed, e.g. when the sheetname or filename contains a space. I added them just to be sure. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference text in a cell inside a formula to specify a sheet name | Excel Worksheet Functions | |||
How to embed large number of variables in text inside a text box? | Excel Discussion (Misc queries) | |||
using a formula inside a sting of text | Excel Discussion (Misc queries) | |||
How to make the text inside Text Box upside down? | Excel Discussion (Misc queries) | |||
Need macro to insert text string while inside cell (formula) | Excel Discussion (Misc queries) |