Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup from one spreadsheet that links to matching keys in another
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
how do you change pathways (links) when consolidating spreadsheet. | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |