Hi: Can any one please tell me how to i create a lookup sheet. I mean every time when i open the excel file it automatically update the data into the sheet from another excel file.

Thanks.

Howdy. It works best to have both workbooks open when setting this up. Depending on which form of lookup you use (LOOKUP, VLOOKUP, or INDEX/MATCH), the form will vary but the reference is still valid. Here is one example using INDEX/MATCH:

=INDEX([Data.xls]!$A2:$K$100,MATCH($A18,[Data.xls]!$A$2:$A$100,0),E$14)

Note, that the workbook reference is in square brackets.

ok thanks only last thing which is my target workbook and which one is source

Data in the example above is the source.