SOLVED
Home

How do create a hyperlink using a formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-797168%22%20slang%3D%22en-US%22%3EHow%20do%20create%20a%20hyperlink%20using%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797168%22%20slang%3D%22en-US%22%3E%3CP%3ECell%20A%20%3D%20%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2F%3C%2FA%3E%3C%2FP%3E%3CP%3ECell%20B%20%3D%20P1-123%3C%2FP%3E%3CP%3ECell%20C%20%3D%20Cell%20A%20%26amp%3B%20Cell%20B%20%3D%20%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-123%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-123%3C%2FA%3E%3D%20great!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20then%2C%20I%20copy%20the%20formula%20down%20and%20this%20happens%3A%3C%2FP%3E%3CP%3ECell%20A%20%3D%20%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2F%3C%2FA%3E%3C%2FP%3E%3CP%3ECell%20B%20%3D%20P1-124%3C%2FP%3E%3CP%3ECell%20C%20%3D%20Cell%20A%20%26amp%3B%20Cell%20B%20%3D%20%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-124%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-124%3C%2FA%3E%3D%20BUT%20-%20when%20I%20click%20edit%20hyperlink%20it%20shows%20P1-123%20-%20is%20there%20a%20way%20to%20adjust%20the%20formula%20or%20equivalent%20so%20that%20is%20shows%20a%20link%20to%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-124%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-124%3C%2FA%3Einstead%20of%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-123%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-123%3C%2FA%3E%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20it%20displays%20at%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-124%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-124%3C%2FA%3Ebut%20when%20clicked%20goes%20to%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.jira.com%2FP1-123%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.jira.com%2FP1-123%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20solve%20this%20without%20VBA%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20current%20formula%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C7%26lt%3B%26gt%3B%22%22%2C%22%3CA%20href%3D%22https%3A%2F%2Fwww.jira.com%2F%26quot%3B%26amp%3BC7%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.jira.com%2F%22%26amp%3BC7%3C%2FA%3E%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-797168%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797187%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20create%20a%20hyperlink%20using%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389745%22%20target%3D%22_blank%22%3E%40MattC475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20try%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DHYPERLINK(A1%26amp%3BB1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-800133%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20create%20a%20hyperlink%20using%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-800133%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389745%22%20target%3D%22_blank%22%3E%40MattC475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFigured%20this%20out%20using%20YouTube.%20Used%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C55%26lt%3B%26gt%3B%22%22%2CHYPERLINK(CONCATENATE(%22%3CA%20href%3D%22https%3A%2F%2Fwww.jira.com%2F%26quot%3B%2C%5BJira%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.jira.com%2F%22%2C%5BJira%3C%2FA%3EId%5D)%2C%20%22%3CA%20href%3D%22https%3A%2F%2Fwww.jira.com%26quot%3B%26amp%3B%5BJira%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.jira.com%22%26amp%3B%5BJira%3C%2FA%3EId)%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
MattC475
Occasional Contributor

Cell A = www.jira.com/

Cell B = P1-123

Cell C = Cell A & Cell B = www.jira.com/P1-123 = great!

 

However, then, I copy the formula down and this happens:

Cell A = www.jira.com/

Cell B = P1-124

Cell C = Cell A & Cell B = www.jira.com/P1-124 = BUT - when I click edit hyperlink it shows P1-123 - is there a way to adjust the formula or equivalent so that is shows a link to www.jira.com/P1-124 instead of www.jira.com/P1-123??

 

In short, it displays at www.jira.com/P1-124 but when clicked goes to www.jira.com/P1-123.

 

Is there a way to solve this without VBA?

 

My current formula is:

 

=IF(C7<>"","https://www.jira.com/"&C7,"")

 

 

2 Replies
Solution

@MattC475 

Did you try

=HYPERLINK(A1&B1)

@MattC475 

 

Figured this out using YouTube. Used the following formula:

 

=IF(C55<>"",HYPERLINK(CONCATENATE("https://www.jira.com/",[Jira Id]), "https://www.jira.com"&[Jira Id),"")

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies