Auto Filling one sheet from another

[Titanus]

Does MATCH and INDEX work for this? If anyone has a solution for this,
I would be grateful!

In one spreadsheet I have two worksheets. Sheet 1 has a Column A
(Date: numbered sequentially, low to high), and the next three columns
(header A, B, C) contain closing stock prices for Stock A, Stock B,
Stock C. The data is NOT in sequential order (so B2 might be > B3 and
<<B4), but is paired to the DATE (column A) that price occurs. Far
across the spreadsheet, after a bunch of data work has been done on the
stock prices (we'll say Column AA) I am creating a buy or sell signal
for whatever stock matches my criteria; the value returned is the
header of the respective stock, so I know whether, on a certain date, I
should have bought A, B, or C. So in Column AA there may not be ANY
data until AA20, in which the return value might be A, and then maybe
the next cell down (AA21) is C, then back to A in AA22. Column AB is
the same thing, except these are SELL signals, with value returns of A,
B, or C.

On worksheet two I have Column A as the date (same as sheet 1) then the
three stock columns the same as sheet 1, each separated by an empty
column (so Stock A is column B, Stock B is column D, Stock C is column
F) . What I WANT to do is return to this sheet the markers from Sheet
1, column AA and AB. SO, let's say on Sheet1!AA20 = A, Sheet1!AA21 =
C, Sheet1!AA22 = B (in the To Buy column). On sheet 2 I want B20 = 2
(my signal for BUY), D22 = 2, and F21 = 2.

Again, it has to line up perfectly with Stock Name and Date. How do I
parse individual placings to another page? Oh, and there will always
be 1 and only 1 value to a cell on sheet 1.

Thanks for the help!

-Mr. T

 

[Max]

Just venturing a guess ..

In Sheet2,

In A20, copied down: =IF(Sheet1!AA20="A",2,"")
In D20, copied down:=IF(Sheet1!AA20="B",2,"")
In F20, copied down:=IF(Sheet1!AA20="C",2,"")

And likewise for your sell signals in the cols adjacent
to the above buy signal cols except pointing to col AB in Sheet1

 

[Max]

Typo, sorry:

In A20, copied down: =IF(Sheet1!AA20="A",2,"")

should read:

In B20, copied down: =IF(Sheet1!AA20="A",2,"")

---

 

[Titanus]

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)?

 

[Max]

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 D2030, 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]

Slight revision ..

In Sheet2,

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

For a cleaner looking output, better to use:

Put in A20, copy down to A30:

=IF(Sheet1!A20="","",Sheet1!A20)

---