Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.