Formula to Link to Specific List on Another Worksheet

[jdurrmsu]

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#...|.....QTY...|
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.

[Max]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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#...|.....QTY...|
> 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
>