Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() I am trying to figure out how to link a cell to a list on another worksheet. What I would like to do is have a function that searches an array of cells against a fixed cell and return a link to a list on another page that matches the fixed cell. For example, this is what I would like to do. On Sheet1 all of the columns are shown as lists. Sheet1....|....Col A..........|....Col B....|.....Col C....| Row 1.....|....PO#............|....Item#..|......QTY.. ..| Row 2.....|....LL-16-1.......|........1......|......10.......| Row 3.....|....LL-16-1.......|........2......|......15.......| Row 4.....|....LL-16-1.......|........3......|......20.......| Row 5.....|....LL-16-2.......|........1......|......11.......| Row 6.....|....LL-16-2.......|........2......|......21.......| Sheet2...|....Col A.............|.....Col B..........|....Col C....|....Col D...| Row 1.....|....Link Function..|.....PO#...........|....Item#...|.....Q TY...| Row 2.....|....A2 Function....|.....LL-16-1......|.......1........|......10.....| Row 3.....|....A3 Function....|.....LL-16-1......|.......2........|......15.....| Row 4.....|....A4 Function....|.....LL-16-1......|.......3........|......20.....| Row 5.....|....A5 Function....|.....LL-16-2......|.......1........|......11.....| Row 6.....|....A6 Function....|.....LL-16-2......|.......2........|......21.....| For example I would like the A2 function to work like this. If Sheet2!B2 = Sheet1!A2:A6 then provide a link in Sheet2!A2 that directs you to Sheet1 and only displays the rows where the PO#’s in Sheet1 A2:A6 match B2 on Sheet2 which in this case would link to rows 2, 3, & 4 on Sheet1. Like I said this is only an example and in reality I have about 2000 rows with entries. I do not know if there is even a way to do this but if anyone has any suggestions I would really appreciate it. Thanks. -- jdurrmsu ------------------------------------------------------------------------ jdurrmsu's Profile: http://www.excelforum.com/member.php...o&userid=27122 View this thread: http://www.excelforum.com/showthread...hreadid=508172 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Here's a non-array formulas play which provides the core "auto" filtering
functionality from another sheet that's desired here .. A sample construct is available at: http://www.savefile.com/files/3781433 Auto_Filtering From Another Sheet_jdurrmsu_wks.xls In Sheet1, Source data is in cols A to C (Headers in A1:C1 : PO#, Item#, Qty), data from row2 down Put in E2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) Put in F2: =INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0)) Select E2:F2, copy down to say, F10, to cover the max expected extent of data (Col F auto-extracts the unique list of PO#s from col A) Click Insert Name Define Put it as: "Name in workbook:" PO_num Under "Refers to:" =OFFSET(Sheet1!$F$2,,,SUMPRODUCT(--(NOT(ISERROR(Sheet1!$F$2:$F$10))))) Click OK (Adapt the range $F$2:$F$10 to suit the actual extent of data) The above set-ups will enable us to use a data validation [DV] list to select the desired PO# in Sheet2 In Sheet2, The same col headers are pasted into A1:C1, viz.: PO#, Item#, Qty Let's create a DV list for use to select the PO# (the unique PO# list): Select A2, click Data Validation Make the settings as: Allow: List Source: =PO_num Click OK Select say: LL-16_1 from the droplist Put in B2: =IF(ISERROR(SMALL($D:$D,ROW(A1))),"", INDEX(Sheet1!B:B,MATCH(SMALL($D:$D,ROW(A1)),$D:$D, 0))) Copy B2 to C2 Put in D2: =IF(Sheet1!A2="","",IF(Sheet1!A2=$A$2,ROW(),"")) Select B2:D2, fill down Cols B & C will auto-return all lines (for Item# and Qty from Sheet1) matching the PO# in A2, neatly bunched at the top Test it out by selecting another PO# from the droplist -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jdurrmsu" wrote in message ... I am trying to figure out how to link a cell to a list on another worksheet. What I would like to do is have a function that searches an array of cells against a fixed cell and return a link to a list on another page that matches the fixed cell. For example, this is what I would like to do. On Sheet1 all of the columns are shown as lists. Sheet1....|....Col A..........|....Col B....|.....Col C....| Row 1.....|....PO#............|....Item#..|......QTY.. ..| Row 2.....|....LL-16-1.......|........1......|......10.......| Row 3.....|....LL-16-1.......|........2......|......15.......| Row 4.....|....LL-16-1.......|........3......|......20.......| Row 5.....|....LL-16-2.......|........1......|......11.......| Row 6.....|....LL-16-2.......|........2......|......21.......| Sheet2...|....Col A.............|.....Col B..........|....Col C....|....Col D...| Row 1.....|....Link Function..|.....PO#...........|....Item#...|.....Q TY...| Row 2.....|....A2 Function....|.....LL-16-1......|.......1........|......10.....| Row 3.....|....A3 Function....|.....LL-16-1......|.......2........|......15.....| Row 4.....|....A4 Function....|.....LL-16-1......|.......3........|......20.....| Row 5.....|....A5 Function....|.....LL-16-2......|.......1........|......11.....| Row 6.....|....A6 Function....|.....LL-16-2......|.......2........|......21.....| For example I would like the A2 function to work like this. If Sheet2!B2 = Sheet1!A2:A6 then provide a link in Sheet2!A2 that directs you to Sheet1 and only displays the rows where the PO#’s in Sheet1 A2:A6 match B2 on Sheet2 which in this case would link to rows 2, 3, & 4 on Sheet1. Like I said this is only an example and in reality I have about 2000 rows with entries. I do not know if there is even a way to do this but if anyone has any suggestions I would really appreciate it. Thanks. -- jdurrmsu ------------------------------------------------------------------------ jdurrmsu's Profile: http://www.excelforum.com/member.php...o&userid=27122 View this thread: http://www.excelforum.com/showthread...hreadid=508172 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Copying a Formula which contains a link to another worksheet | Excel Discussion (Misc queries) | |||
Worksheet name / reference as a formula? | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
how to reference external refereces from a list | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |