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?