"Titanus" wrote:
Thanks, Max. That's what dawned on me yesterday.
That'll work as long as I keep Sheet 2 identical to Sheet 1
as far as format goes.....but what if the format changes
(say I cut some rows out), how can I link the results
in Sheet 1 to Sheet 2, using Date (value in Column A) and
Stock Name (header value in columns A-C)?

In Sheet1,

Assuming we have
dates in A20:A30,
buy signals in AA20:AA30 (A, B, C ..)
sell signals in AB20:AB30 (A, B, C ..)
eg:

.................Buy Sell
04-Apr-06 A B
05-Apr-06 C A
06-Apr-06 B C
etc

then in Sheet2:
we have the stocks labelled
in B1: A, in D1: B, in F1: C

Put in A20, copy down to A30: =Sheet1!A20
(this simply links the dates over from Sheet1's A20:A30)

Put in B20, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,(Sheet1!$A$20:$A$30=$A20)*(Sheet1 !$AA$20:$AA$30=B$1),0)),""
,2)
Copy B20 to B30

Then copy B20:B30 and paste to D20:D30, and to F20:F30

The above will return the buy signal "2"
corresponding to the dates within A20:A30
for the stocks labelled in B1, D1, F1

Similarly, for the sell signals
(assume the sell signal's a "1",
and we are to continue to point to B1, D1, F1 for the stock labels)

Put in C20, array-enter, copy to C30:
=IF(ISNA(MATCH(1,(Sheet1!$A$20:$A$30=$A20)*(Sheet1 !$AB$20:$AB$30=B$1),0)),""
,1)

Then copy C20:C30 and paste to E20:E30, and to G20:G30

(Formulas for sell are identical to that for the buy signals,
except pointing to col AB in Sheet1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---