External Links

[PeterG]

Whats the best method to link 2 spreadhseets together. They have a stock code that is the common link between them. 1 sheet has 4168 lines the other has 14909 lines. The larger one is a FULL list of all stock codes, the other is a smaller subset, its needed for sections of catalogues. I want to be able to change the price on the master list and have it updated on the subset listing.

At the moment I have setup a hiddeous lookup formula - rather messy.

Thanks
Peter.

[Paul Sheppard]

Whats the best method to link 2 spreadhseets together. They have a stock code that is the common link between them. 1 sheet has 4168 lines the other has 14909 lines. The larger one is a FULL list of all stock codes, the other is a smaller subset, its needed for sections of catalogues. I want to be able to change the price on the master list and have it updated on the subset listing.

At the moment I have setup a hiddeous lookup formula - rather messy.

Thanks
Peter.

Hi Peter

VLOOKUP seems to be your best bet viz >

=VLOOKUP(A1,'C:\My Documents\[WorkbookName.xls]WorksheetName'!$A$1:$B$14909,2,0)

You will need to change the cell reference, full path C:\..... to match your file location, WorkbookName, WorksheetName, range and the column no the price is in

Drag the formula down to row 4168 (or 9 if you have a header row)

If you don't want to use lookup you could just create a direct link, in the workbook with 4168 records in the price column for item 1 type =, then go to the price column in the workbook with 14909 records and click in the price cell corresponding to the item in the first workbook and enter, repeat for the 4167 items this will be time consuming as you would have to match each record separately,