-1

I am tracking several stocks, and saving their chart data in separate spreadsheets titled by their ticker symbol (i.e. aapl.xlsx, goog.xlsx, etc...). I want to have a single spreadsheet that I can change the text in a cell to the ticker symbol so that it will reference the proper file for that stock, thereby allowing me to pull a different stock's data each time I change the cell. All of the data is on a page called "1", so I need to be able to have a way to reference a text cell and input it into a formula such as:

=rounddown([XXXX.xlsx]1!D4-[XXXX.xlsx]1!H4,2)*[XXXX.xlsx]1!B4

where XXXX is the text that I input to a cell on my new spreadsheet. I've been trying to use different ways to reference it using the INDIRECT( function, but I haven't been able to get that to work yet.

Is Excel capable of taking a text input and placing it into a formula? if possible how would this be achieved?

1

2 Answers 2

0

Do you have all your stock files open?  You will need to do that.  According to the Excel 2007 Help for the INDIRECT function,

If ref_text refers to another workbook (an external reference), the other workbook must be open.  If the source workbook is not open, INDIRECT returns the #REF! error value.

0

This kind of depends on whether the cell you are referencing is a static cell (e.g., the value is always in A4 or 'Grade 11 Data'!A4).

If not, then you do need to use the INDIRECT() function. You would use it like INDIRECT("K"&A4) where A4 contained a row number, say 9. Then it resolves to INDIRECT("K9") and that would then resolve to the cell K9.

You can also use stuff like INDIRECT("'Grade 11 Data'!K"&A4), which would resovle to INDIRECT("'Grade 11 Data'!K9"), which would resolve to cell 'Grade 11 Data'!K9.

An example for INDIRECT is that I had a sheet where I would enter a lot number and it would reference a different cell depending on the lot number I entered (A4 if I enter 4, A7 if I enter 7, A8 if 8, ....) to find shipping values and strings that vary depending on which lot I am referring to.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .