vlookups

Hi,

I'm trying to create a new costing form which takes product costing details from numerous excel files with cost information on.

I have two sheets on the costing form. Sheet 1 is the main page which will have all of the required information on. In the first column the user will input the section number (which corresponds to the file the information should be found in) and in the second column the user enters the part number. The rest of the information should then fill in automatically.

Sheet2 shall have a list of section numbers and the corresponding VLOOKUP formulas to find the correct info.

So Sheet 2 has this formula: =VLOOKUP(Sheet1!B2,'K:\Product Database\2 - PRODUCTS.xls'!test,6,FALSE)
Sheet 1 has this: =VLOOKUP(A2,Sheet2!A1:B1,2,FALSE)

So sheet 1 is bringing the VLOOKUP from Sheet2 based on the section number in the first column. That VLOOKUP then checks the file relating to that section for the part number an brings the costing information across.

The issue with this is that Sheet 1's formula will always bring back a VLOOKUP looking for the part number in cell B2. How do I amend the Sheet 2 formula so that when it is brought across to Sheet 1 it looks in column B of the row it is on? I believe it is the bit in bold that needs changing?

THanks

Describe problem and your desired results at workbook for clear picture, and upload it at the forum. To do that, click "Go Advanced" button and find Paperclip Button to attach the file.

Thanks

Thanks for the help. I ended up doing a massive if formula i.e. if c21=1 vlookup here, if it =2 vlookup here...

Now to try and get hyperlinks to other files to copy across...