Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching records and linking to other sheets if true
let me explain it to u in detail....suppose i m making a record of an college
which includes name,permanent addess,hostel name,phone no etc....now i also want that i get hostel specific records also(for that i have made diiff sheets namely hostel 1,hostel 2 etc)...i want that when i enter hostel 1 in the hostel coumn of the main sheet, the name,address etc go to the sheet of hostel 1 automatically.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching records and linking to other sheets if true
Here's a formulas driven model which delivers the required automation ..
Illustrated in this sample: http://freefilehosting.net/download/40eb5 Parent to Child AutoCopy Model_KeyCol D.xls In sheet: WS1 (the "master"/"parent" sheet) Data in cols A to E, from row2 down, with the key col = col D (as per spec) List the key col values (col D's unique values) in M1 across, eg: Hostel1, Hostel2, etc (list can be in any order, but must match exactly with what's on the tabs, except for case) Put in M2: =IF($D2="","",IF($D2=M$1,ROW(),"")) Copy M2 across & fill down to cover the max expected extent of source data in the key col D 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: Hostel1 With the same col headers pasted into A1:E1 Put in A2: =IF(ROWS($1:1)COUNT(OFFSET(WS1!$L:$L,,MATCH(WSN,W S1!$M$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1, 0)),0))) Copy A2 across to E2, fill down to say, E10 (copy down by the smallest possible range sufficient to cover the max expected extent for any key col value. Here, I've assumed that 9 rows (rows 2 to 10) is sufficient) Cols A to E will return only the lines for the key col value: Hostel1 from "WS1", with all lines neatly packed at the top Dress this sheet up nicely to taste, then just make copies of it, rename as the other key col values: Hostel2, Hostel3, etc to get corresponding returns. Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,600 Files:362 Subscribers:60 xdemechanik --- "Anil Singla" wrote: let me explain it to u in detail....suppose i m making a record of an college which includes name,permanent addess,hostel name,phone no etc....now i also want that i get hostel specific records also(for that i have made diiff sheets namely hostel 1,hostel 2 etc)...i want that when i enter hostel 1 in the hostel coumn of the main sheet, the name,address etc go to the sheet of hostel 1 automatically.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Records | Excel Worksheet Functions | |||
Matching 2 figures if not =0 rtn true | Excel Worksheet Functions | |||
Matching records | Excel Discussion (Misc queries) | |||
Indexing/Matching True/False results | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |