Thread: Array formula + Indirect Reference

I am trying to do the following two procedures:

Procedure 1.

Array Formula setting.... for stock analysis.
The formula syntax is,

=QLink|Bars!'SYMBOL,PERIOD,#BARS,[DTOHLCV],{HEADERS},{HH:MM-HH:MM},{FILL}'

Example: =QLink|Bars!'IBM,15,1000,DTOHLCV,HEADERS,09:30-16:00'

I have copied the formula into multiple sheets (a separate sheet for each stock/symbol).

The SYMBOL field, of the formula, is the only field that changes on each sheet.

Instead of me typing in the different/new symbol manually on each sheet, is possible to reference the 'symbol' to a cell value (eg. $a$1 or if it is easier, to reference it to the sheet/tab name). I tried using the $A$1 format but it does not work ... i guess something to do with the apostrophe before the symbol text.
Another option, if it may be easier, can I group the sheets, select the cell that contains the array formula - and somehow 'find and replace' the 'symbol' text with a cell value (in this case the stock symbol in A1)
...............................

Procedure 2.

I have data for multiple stocks in multiple files/workbooks (because of size constraints of Excel).

I have another workbook that has an analysis template/sheet.
There are 2 data columns in the sheet - one each for a pair of symbols.
There are two empty cells in the template - one for each of the 2 stock/symbols.
When I type a symbol into one of the empty cell - I would like the template to fetch the data for that symbol, from the data workbooks, and paste/download it into the respective column for the stock.

My challenge here is: I think I can try the INDIRECT reference method.
What I do not know is, how to create the formula, such that, the template would search all the workbooks, and find the sheet for the particular symbol. By myself, I can manage creating a formula if there was only one workbook/file to create an INDIRECT reference.

Of course, I could manually type in the respective workbook/file each time, but obviously I am trying to bypass doing so.
.............................................

Thanks much for any feedback.