Hyperlinks returns reference isn't valid

Hello, new user here and this is my first post. Hopefully i make things easy because im not good at excel.

Look at the sample worksheet, there are two tabs, TRADESHEET and Invetory list.

in the TRADESHEET the cells A7 is a dependent drop down list that reads its value from A6. The list itself is in the Invetory list tab.

The list in the Invetory list tab have heyperlinks that takes you to cell ranges on TRADESHEET tab when clicking a product.

What i need is when i select a product from drop down list in cell A7 from TRADESHEET tab and click on it, it should behave similary to clicking that same product from the Invetory list tab and take me to the appropriate cell range location.

Currently cell A7 from TRADESHEET tab shows that theres hyperlink there but when clicked is says reference isnt valid.

How do i make this work? I've followed few online tutorials and youtube videos but nothing works.

Thanks for your help in advance.

Hi JeteMc, thanks for the reply.

Figured i cant post links yet but please see attached text file for links

Thank you.

Thanks JeteMc

Ill see what I can do with the formula because the work book I attached here just sample, actually workbook is huge with multiple selection ranges across and down.