Hyperlinking via/from Data Validation List? Only simple solution needed!

Hello.

I've uploaded a dummy of the work book, you'll instantly see what is trying to be achieved... It's quite simple. Though clearly beyond me - for the moment (until you wonderful people help!!). I have googled hard and low for a solution! And the closest solution I was looking for actually came from this forum. It was this thread in fact. I wanted to attempt the embedded hyperlink JBeaucaire put forward but the syntax made no sense to me. But after some thought, it would be highly preferable to not add any other columns unnecessarily as I want the worksheet to be completely visible, eliminating the need of side-scrolling.


This what I would like my spreadsheet to do;

- To have data validated cells in the "CONTACT SHEET" (columns G & F) pull through hyperlinks as defined on the "DATA Validation Sheet".

- The "DATA Validation Sheet" only has two named ranges with hyperlinks; venue (A2:A4) and childunit (A2:A4).

- The data validation cells (Columns G & F) on the "CONTACT SHEET" must be able to accommodate 500 rows.

--

If you can link to specific cells across different workbooks, that would be perfect! As I'd prefer to maintain separate workbooks for both the Client Lists and Venue Lists. In addition, if anyone could just sign post me to the relevant rules to have the line manager automatically assigned once the supervisor is selected.. I've speculated it will use VLOOKUP along with IF.

If anyone could help I'd be grateful, as will the rest of my colleagues when they see how much smoother this can be!
-Brian

Explain your requirement with Sheet / Cell / Range references and the method for giving exact solution