Microsoft Excel - How can I change a destination link inside a formula according to another cell's value

I have excel files for every single tennis player, using them for database. All the files are been interacted, because I exchange values between them manually. What I want is to save my time, by replacing my manually exchange values with a formula.

In every line I store a single match data (stats), where I also store the name of opponent player. What I need is to use this opponent's name inside a formula, which will be in every line after that (everytime for the respective opponent player), in order to have the link of the opponent's cell. 

How can I do that?

Thank you

Hi Christos,
I imagine you would be able to do what you want by using INDIRECT and use the cell contents to construct the address for the relevant cell on the other worksheet.  I'm a bit busy at the moment but will try to get a working example for you a bit later.
Regards
Harry

Hi Christos,
Assume that you have sheets named Pat Rafter and Andre Agassi.  On sheet Pat Rafter you have Andre Agassi entered in cell C2 on sheet Pat Rafter.  This formula on sheet Pat Rafter will show the value in Column B from the corresponding row  on sheet Andre Agassi

=INDIRECT("'"&C2&"'!B"&ROW(C2),TRUE)

Not sure whether this is what you want but hopefully you will be able to modify it to suit.

Regards
Harry

Hello Harry and thank you so much for your help. 

I am going to test what you suggest and I'll be back.

Thank you again.

This is a picture with a view of two files side by side: http://i389.photobucket.com/albums/oo339/superduper_032/tennissample.png

As you can see, I put Berdych's H and I columns values to Djokovic's M and N columns, and Djokovic's H and I column values to Berdych's M and N columns. I do this manually and for every match (as you can see above lines). In AA column is the opponent's player name.

You think what you suggest is what I need?

thank you one more time and many more!

Hello Christos,
From that image, I see that it is going to be a bit more complicated as the row numbers do not coincide and there appear to be multiple worksheets associated with each player.  Off the top of my head I would say that you  might need at least some VBA to manage the matching of records and it might be better to be considering a relational database rather than Excel to manage this sort of data.
I will think about it some more and get back to you in due course.
Regards
Harry

It will be "much" appreciated!

Hello Christos,
I think this is going to end up being unworkable because you will have multiple links between multiple workbooks.
Another problem is that there has to be a master record where the data is entered and the slave record that picks up the value by formula and exactly how that is managed could be quite complicated.
For a simple case where there is a single worksheet (named with the players name) for each player in a single workbook this formula works by matching the date of the match to find the relevant row to get the data from.
=INDIRECT("'"&AA496&"'!M"&MATCH(Z2,INDIRECT("'"&AA496&"'!Z:Z"),1),TRUE)
The column letters inside quotes need to be changed to read from different columns but copying down in the one column will work fine.
If you wanted to try linking between workbooks you would have to alter the worksheet reference to include the complete path name to the directory where your files are stored with the file name being the dynamic component and also the sheet name as well.
Using VBA might be a bit less messy but still has to deal with writing data between multiple workbooks and keeping track of master/slave relationships.
Hope this helps.
Harry

Harry, thanks again for your time to reply. I will test your idea and I will let you know if any problem. Of course, I will have in mind the problems that will be arise, as you described.