Locking a hyperlink to information in another workbook problem!

[oliv3rp]

Hi everybody!

Currently I have two separate workbooks, one for national market intelligence and one containing export documents for different countries.

I want to be able to create a hyperlink in the NMI database to a particular cell in the ED database.
So in the first one, currently I have added a hyperlink with the address of the second document and added a bookmark with the cell I want in the second.

HOWEVER...

If I now edit the second database, for instance by adding a new row at the very top, every other cell will drop (A1 becomes A2 etc.) meaning that if I currently have data in E40 linked to the first document, the link would now show whatever is currently in E39.

With that example, every cell in the entire database would change and over time I plan if possible on having an awful lot of direct links to specific cells.

Is there a way at all to link perhaps to the content of the cell, whereby if it moves, the link will follow?

I'm pretty certain I'm asking a bit too much of the software here but you never know so fingers crossed!


I've tried to make this as clear as possible!
Thanks

[Pete_UK]

I've put a couple of examples together for you in the attached file, so you can see how this can be achieved.

In the sheets Page001 and Page002 there is a list of numbers in column A, one in ascending order and the other in descending order, and in column B there is a hyperlink formula which allows you to jump to the corresponding number in the other sheet -these formulae use the R1C1 notation.

In Sheet1 there is a data validation cell (A1, coloured yellow) where you can select a name from the drop-down. If you click on cell A2 you will be taken to the cell containing that name in the Testsheet. The data validation uses the named range "names", as defined on that Testsheet.

Obviously in your case you will need to include the full path, filename and sheetname to the (external) file, but I hope it gives you some ideas of how to do it.

Hope this helps.

Pete

[oliv3rp]

I can see what you've done and tested it by adding cells and moving them. That's exactly what I'm after...

However, wow this is complicated haha!

This might be me being silly but could you just type out the formula in here and use dummy file locations or any other variables for me to insert where appropriate in order to test it out!

Only ever used basic excel before so I'm not a whizz!