ExcelBanter - Lookup from one spreadsheet that links to matching keys in another

Hi cac,

If the aim is to minimise the target sheet's size, it would be best to do
all the filtering in a worksheet attached to the source workbook, then
simply use an external link that points to the source workbook's filtered
range.

As to how to construct the filtered table, you could use a formula like:
=INDEX(Data,MATCH(Criterion,UnfilteredCriteria,0))
for the first output row, which I'll assume to be row 2 (row 1 being a
heading row), and:
=IF(OR(A2="",COUNTA(A$2:A2)=COUNTIF(UnfilteredCrit eria,Criterion)),"",INDEX(
Data,MATCH(Criterion,OFFSET(UnfilteredCriteria,MAT CH(A2,Data,0),0,COUNT(Unfi
lteredCriteria)+1-MATCH(A2,Data,0),1),0)+MATCH(A2,Data,0)))
for row 3 and copied down as far as necessary.

You'll note that I've used three named ranges. Doing so makes it easier to
see what each part of the formula is referring to, though not necessarily a
whole lot easier to undestand. the 'Criterion' name refers to a single cell
that determines which cells from the 'UnfilteredCriteria' range to match
with, whilst 'Data' defines the range from which you actually want to
retrieve the corresponding contents.

Cheers

"cac1231" wrote in message
...
I have header and detail type information, and would like to use the
filter
function on the header spreadsheet then have the spreadsheet link to the
detail spreadsheet and just display the rows associated with the data in
the
filter from the header.

Worst case I can combine the two spreadsheets into one via vlookup and
just
repeat the header information for every detail line but would like to keep
the smaller file size associated with holding the data once.