Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort between worksheets depending on variable
Excel formula
I need to sort or transfer the data entered into one sheet to automatically show on another sheet within the same workbook but depending on the variable. For example in Sheet A I have names in cells A2 - A10 and in cells B2 - B10 assigned them a value of either L (left) or R (right). I want these names exported to different sheet depending on whether they are L or R. In the order found in the original sheet. Excel 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort between worksheets depending on variable
Here's an easy non-array formulas driven model
which delivers exactly what you seek to do .. Illustrated in this sample: http://freefilehosting.net/download/40aef Parent to Child AutoCopy Model.xls In sheet: WS1 (the "master"/"parent" sheet) Data in cols A & B, from row2 down, with the key col = col B (as per reqt) List the key col values (col B's unique values) in K1 across: L, R (can be in any order) Put in K2: =IF($B2="","",IF($B2=K$1,ROW(),"")) Copy K2 across & fill down to cover the max expected extent of source data in the key col B 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: L With the same col headers pasted into A1:B1 Put in A2: =IF(ROWS($1:1)COUNT(OFFSET(WS1!$J:$J,,MATCH(WSN,W S1!$K$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1, 0)),0))) Copy A2 across to B2, fill down to say, B10 (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 & B will return only the lines for the key col value: L from "WS1", with all lines neatly packed at the top Dress this sheet up nicely to taste, then just make a copy of it, rename as the other key col value: R to get corresponding returns. Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Miriaham" wrote: Excel formula I need to sort or transfer the data entered into one sheet to automatically show on another sheet within the same workbook but depending on the variable. For example in Sheet A I have names in cells A2 - A10 and in cells B2 - B10 assigned them a value of either L (left) or R (right). I want these names exported to different sheet depending on whether they are L or R. In the order found in the original sheet. Excel 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide and protect worksheets depending upon who is looking? | Excel Discussion (Misc queries) | |||
Reading Data from another workbook... depending on variable in a cell? | Excel Discussion (Misc queries) | |||
Sort : how can I use a variable in a VB sort function? | Excel Worksheet Functions | |||
How to read CSV lines into different Worksheets depending on a date field? | Excel Discussion (Misc queries) | |||
Compare 2 Worksheets Create a 3rd depending on results | Excel Discussion (Misc queries) |