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 B22, 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