Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Titanus" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
Typo, sorry:
In A20, copied down: =IF(Sheet1!AA20="A",2,"") should read: In B20, copied down: =IF(Sheet1!AA20="A",2,"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
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)? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
"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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Filling one sheet from another
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto numbering in each sheet of a workbook | Excel Discussion (Misc queries) | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
Auto Protecting cells & auto filling date | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |