| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help - Linking worksheets
Thank you so much, this was exactly what I was looking for.
"Max" wrote: .. I have 12 other worksheets each named by Months. It's better to be unambiguous and name the 12 "child" sheets as: Jan08, Feb08, etc (with the month/yr, rather than just the month) Ok, here's one play which delivers what you're after. It auto-copies lines from a master "parent" sheet by the date (key col) into the respective month/yr "child" sheets using non-array formulas. In each mth/yr child sheet, lines will be neatly bunched at the top and will also appear sorted in ascending order by the date (additional bonus!) The working set-up is illustrated in this sample: http://www.freefilehosting.net/download/3db6c AutoCopy Lines to Resp Sht Non Array_Dates.xls In sheet: A (the "master") Assume data in cols A to C, data in row2 down, with the key col = col A (Dates) List the 12 "child" sheetnames in K1 across: Jan08, Feb08, Mar08, etc (can be in any order) (do note that the sheetnames are entered as **text** with a preceding apostrophe) Put in K2: =IF($A2="","",IF(TEXT($A2,"mmmyy")=K$1,$A2+ROW()/10^10,"")) Copy K2 across as far as required, then fill down to cover the max expected extent of source data Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Technique came from a post by Harlan. In a new sheet named: Jan08 With the same col headers pasted into A1:C1 Put in A2: =IF(ISERROR(SMALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1 :$IV$1,0)),ROWS($A$1:A1))),"",INDEX(A!A:A,MATCH(SM ALL(OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1,0)),ROW S($A$1:A1)),OFFSET(A!$J:$J,,MATCH(WSN,A!$K$1:$IV$1 ,0)),0))) Copy A2 across to C2, fill down to say, C10 (copy down by the smallest possible range sufficient to cover the max expected extent for any month/yr. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient) Cols A to C will return only the lines for Jan08 from "WS1", with all lines neatly bunched at the top and sorted in ascending order by date Now, just make a copy of Jan08, rename it as the next one: Feb08, and we'd get the results for Feb08. Repeat the copy rename sheet process to get the rest of the 12 mth/yr sheets (a one-time job). Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ceci" wrote: Thank you so much however, I have another question. Assume the dates running down column A in sheet A (Master sheet) are random. I have 12 other worksheets each named by Months. I need the information which for example has the date from sheet A in January in the "January" worksheet, and records in February in the "February" worksheet and so on. Please help because I've been trying to figure this out for a couple of weeks now and have no idea what i'm doing. Ceci |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking worksheets | Excel Discussion (Misc queries) | |||
| linking worksheets | Excel Discussion (Misc queries) | |||
| linking worksheets | Excel Worksheet Functions | |||
| Linking worksheets | Links and Linking in Excel | |||
| Linking worksheets | Links and Linking in Excel | |||
Threaded Mode
