Excel gurus - help request

[SlinkyMike]

I have seen people do awesome stuff with Excel - unfortunately I am not one of them and the need for info on the subject is so great that any search containing the word: 'Excel' is like running a search asking for 'The Internet.'

What I need to do is create a hyperlink to cell on an adjacent worksheet - that much is easy.

Here's the rub: this adjacent worksheet contains definitions, the link needs to remain even if new definitions (rows) are added and the list is re-ordered.

I expect that this cannot be done with a bog-standard hyperlink and would require some form of macro/coding.

Any assistance will be appreciated even if you have suggestions on search terms that may yield a slightly more narrow range of results than: "Entire Intarwebz NOW!"

 

[InSanity]

Have you tried just locking the cell that is referenced im not sure if this will work try this otherwise there are a couple of work arounds i might be able to attempt

 

[Drake2007]

vlookup or hlookup and build your formula around that. also not sure if locking cell will help but try that too.

 

[Jabberwocky]

have you tried defining the list as a table?

 

[Borrels]

Define the destination cell as a Named Range. You will then be able to select the named range as 'a place in the current document' to which it links. You will be able to add rows in between the destination cell and the link should still work. Not too sure about sorting though, cause the actual values in relation to the reference change...

 

[SlinkyMike]

Thanks for suggestions

Managed to use Insert>Name>Define on the target cell then hyperlink to the defined name... had a glimmer of hope when cutting and pating the cell elsewhere maintained the link then I realised I was using a MS prokduct and sorting did not move the defined name with the data so the link remained but in a now empty cell.

This cannot be the intended functionality - it seems obvious to me that this is a bug / MS feature. Can anyone provide some insight that might justify this behaviour?

I'll try locking the cell - hadnt thought of that - also vlookup/hlookup seems worthy of a googling ...thanks again.

 

[Drake2007]

Seems the issue is the spreadsheet is coming close to requiring database behaviour, just an idea but
that vlookup will work with an index column (like a key) so if possible insert that and fill it sequentially then reference out that way.

 

[SlinkyMike]

Seems the issue is the spreadsheet is coming close to requiring database behaviour, just an idea but
that vlookup will work with an index column (like a key) so if possible insert that and fill it sequentially then reference out that way.

This is the solution... just been tinkering with a few rows to prove the concept, thanks all.