Prev: IF formula
Next: Worksheet several pages, Want name to show up on each page
From: davey11372 on 14 Dec 2009 22:52

I am trying to do the following two procedures:

Procedure 1.

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

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

Example: =eLink|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




--
davey11372
 | 
Pages: 1
Prev: IF formula
Next: Worksheet several pages, Want name to show up on each page