Hyperlink within IF function

[lukethayer]

Hopefully you guys have an easy answer to this...

I'm creating a tool for some of my colleagues to use at work. So far, I have a series of check boxes setup that help the user determine if this is the proper tool or not. Once the appropriate check boxes are filled, I'm trying to get excel to give the user a link to the next step.

I have =IF(AND(followonconst=TRUE,Commercial=TRUE,fixedprice=TRUE),?????, "This tool cannot be used")

The names within the 'AND' operation are the references to the output of the check boxes, and all three must be true. What I'm trying to figure out is what I need to put where the question marks are.

Here's what I want to happen...Once all three boxes are checked, I want a link to show up that takes them to another tab in the same spreadsheet. Here's another possible hitch though, the users will likely save this tool into who knows what directory and rename it all sorts of things.

Let me know if there's more information necessary. I'm also totally open to other approaches to accomplishing this.

Thank you!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Here's a formula that you can try. Modify "#Sheet2!A1" to the address in the same workbook you want to hyperlink.

=HYPERLINK(IF(AND(followonconst,Commercial,fixedprice),"#Sheet2!A1","#"),
IF(AND(followonconst,Commercial,fixedprice),"MyLinkDisplay", "This tool cannot be used"))

One problem in working with formulas that apply hyperlinks conditionally is that the hyperlink style formatting (typically underlined blue font/purple once followed) will persist once applied- even in the condition that there is no active hyperlink.

That can be overcome using VBA instead of or in addition to a Hyperlink formula.
Give this a try and see if the font formatting works for you as is.