Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
Have you read the help document on linking workbooks? –  50-3 Sep 12 '13 at 23:05

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.