IF Formula using drop down list and hyperlinks

**sharper1989** · 03-12-2012, 09:23 AM

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.

**NBVC** · 03-12-2012, 09:42 AM

Try something like:

=IF(A1="No","N/A",HYPERLINK("#"&IF(A1="Yes- Promotion Proposed","'Yes- Promotion Proposed'!A1","'Yes - Business Critical'!A1"),A1))

**sharper1989** · 03-12-2012, 10:36 AM

That is amazing! It works perfectly. Thank you SO much.

Not to push my luck, because you've been incredibly helpful, but is there any way I can also include some extra formula so the target cell displays as blank, when nothing is yet chosen from the drop down list? At the moment it's displaying as '0', which I know will confuse people.

Also, I'm trying to work out where I can enter formula to re-name the way the hyperlinks appear, i.e. include a 'friendly link' (I think that's what excel calls them).

I've tried to have a play around, but I keep nulling the IF formula, when trying to add it in.

Really need to work out how to do nested formulas!

Thank you again. If you could help with the above, you'd become my new hero.

Sophie.

**NBVC** · 03-12-2012, 10:39 AM

=IF(A1="","",IF(A1="No","N/A",HYPERLINK("#"&IF(A1="Yes- Promotion Proposed","'Yes- Promotion Proposed'!A1","'Yes - Business Critical'!A1"),A1)))

Try the above to eliminate the 0 when A1 is blank.

the red/bolded A1 is the "Friendly Name" I've chosen, which is the entry chosen in cell A1. I am not sure what you would rather have?

**sharper1989** · 03-12-2012, 10:57 AM

You're a genius and I am forever in your debt. Worked perfectly, thank you so much.

**NBVC** · 03-12-2012, 11:11 AM

You are welcome.. and good luck in your new job

**sharper1989** · 03-14-2012, 05:42 AM

Hi there,

My boss has now come back and requested another option in the drop down box! This drop down would then need to bring up a different response from the IF formula.

The new option in the drop down menu would be 'Increase to benchmark' and it would need to bring up the word 'None' in the cell with the IF Formula. The 'None' doesn't need to be a hyperlink.

I'm trying to desperately work out where to place this in the IF Formula, you helped me out with above but it doesn't seem to like it!

I'm using =IF(E8="","",IF(E8="No","N/A",HYPERLINK("#"&IF(E8="Yes- Promotion Proposed","'Promotion Proposed'!A1","'Business Critical Increase'!A1"),"Click Here")))

Could you possibly help again? Thank you!

**NBVC** · 03-14-2012, 08:02 AM

You mean like this?

=IF(E8="","",IF(E8="No","N/A",IF(E8="Increase to Benchmark","None",HYPERLINK("#"&IF(E8="Yes- Promotion Proposed","'Promotion Proposed'!A1","'Business Critical Increase'!A1"),"Click Here"))))