IF Formula using drop down list and hyperlinks

Hi there,

I've just started a new job and am having a bit of a problem with one of the tasks I've been set. I would REALLY appreciate any help you can offer, so I don't get fired in my second week!

I've started a spreadsheet, which has a drop down list with three options.

I wanted to turn these three options into hyperlinks, which link to another sheet in the workbook, but I understand that's not possible.

So alternatively, I'm happy to enter a formula in the column next to it, which takes note of which drop down option has been chosen & then displays a hyperlink in that cell.

Only I'm completely lost as to how to do this.

For instance;

My drop down options are;

Yes- Promotion Proposed
Yes - Business Critical
No

On choosing 'Yes - Promotion Proposed' - I need the cell next to it to link to the worksheet 'Promotion Proposed'!
BUT IF 'Yes- Business Critical' is chosen, I need the cell next to it to display the hyperlink to the worksheet 'Business Critical'!
& IF 'No' is chosen, I just need it to read 'N/A' with no hyperlink.

Can anyone help me?! I'd really appreciate it.

THANK YOU!

Sophie.