Build https link within formula using Cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-2843849%22%20slang%3D%22en-US%22%3EBuild%20https%20link%20within%20formula%20using%20Cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2843849%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20Excel%20Online.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pulling%20information%20into%20sheets%20in%20my%20current%20workbook%20from%20a%20separate%20excel%20file%20via%20web%20link%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EA2%3DIFERROR('%3CA%20href%3D%22https%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%3C%2FA%3E%20Documents%2FDriver%2F%5B%3CFONT%20color%3D%22%23FF0000%22%3E2021-10-10%3C%2FFONT%3E-4126%20-%20Sheets.xlsx%5DTrip%20Expenses%20Sheet'!%24A81%2C%20%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20link%20pulls%20the%20information%20I%20am%20looking%20for%20perfectly.%26nbsp%3B%20I%20am%20trying%20do%20a%20bit%20of%20future%20proofing.%26nbsp%3B%20I%20would%20like%20to%20get%20part%20of%20my%20link%20from%20an%20adjacent%20cell%20that%20contains%20the%20date%20portion%20of%20my%20link%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EA2%3DIFERROR('%3CA%20href%3D%22https%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%3C%2FA%3E%20Documents%2FDriver%2F%5B%3CFONT%20color%3D%22%23FF0000%22%3EA4%3C%2FFONT%3E-4126%20-%20Sheets.xlsx%5DTrip%20Expenses%20Sheet'!%24A81%2C%20%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETried%20to%20simply%20concatenate%20onto%20the%20link%2C%20but%20Excel%20says%20that%20there%20is%20an%20error%20in%20my%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EA2%3DIFERROR(%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%3CA%20href%3D%22https%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%3C%2FA%3E%20Documents%2FDriver%2F%5B%3CFONT%20color%3D%22%23FF0000%22%3E%22%26amp%3BA4%26amp%3B%22%3C%2FFONT%3E-4126%20-%20Sheets.xlsx%5DTrip%20Expenses%20Sheet%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E!%24A81%2C%20%22%22)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thought%2Fhelp%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2843849%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-2844001%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20https%20link%20within%20formula%20using%20Cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2844001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119667%22%20target%3D%22_blank%22%3E%40DarrMik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%3CA%20href%3D%22https%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%3C%2FA%3E%20Documents%2FDriver%2F%5B%3CFONT%20color%3D%22%23FF0000%22%3E%22%26amp%3BTEXT(A4%2C%22yyyy-mm-dd%22)%26amp%3B%22%3C%2FFONT%3E-4126%20-%20Sheets.xlsx%5DTrip%20Expenses%20Sheet%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E!%24A81%2C%20%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2847018%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20https%20link%20within%20formula%20using%20Cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222021-10-14_12-57-31.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317423i4E56ED49D5DEA8D5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%222021-10-14_12-57-31.png%22%20alt%3D%222021-10-14_12-57-31.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStill%20giving%20the%20same%20error....Excel%20is%20highlighting%20this%20section%20when%20I%20hit%20OK%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%3E%3DIFERRO%3CFONT%20color%3D%22%23000000%22%3ER(%3C%2FFONT%3E%3C%2FSPAN%3E%3CFONT%20color%3D%22%23000000%22%3E%22%3CA%20href%3D%22https%3A%2F%2Fmydomain.sharepoint.com%2Fsites%2FSheets%2FShared%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2FmyDomain.sharepoint.com%2Fsites%2FSheets%2FShared%3C%2FA%3E%3CSPAN%3E%26nbsp%3BDocuments%2FDriver%2F%5B%3C%2FSPAN%3E%22%26amp%3BTEXT(A4%2C%22yyyy-mm-dd%22)%26amp%3B%22%3CSPAN%3E-4126%20-%20Sheets.xlsx%5DTrip%20Expenses%20%3CFONT%20color%3D%22%23FF0000%22%3ESheet%3C%2FFONT%3E%3C%2FSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E%22%3C%2FFONT%3E%3CSPAN%3E!%24A81%2C%20%22%22)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2847654%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20https%20link%20within%20formula%20using%20Cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2847654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119667%22%20target%3D%22_blank%22%3E%40DarrMik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20know%20anything%20about%20SharePoint%3B%20I%20hope%20that%20someone%20else%20can%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Using Excel Online.

 

I am pulling information into sheets in my current workbook from a separate excel file via web link:

 

A2=IFERROR('https://myDomain.sharepoint.com/sites/Sheets/Shared Documents/Driver/[2021-10-10-4126 - Sheets.xlsx]Trip Expenses Sheet'!$A81, "")

 

The above link pulls the information I am looking for perfectly.  I am trying do a bit of future proofing.  I would like to get part of my link from an adjacent cell that contains the date portion of my link:

 

A2=IFERROR('https://myDomain.sharepoint.com/sites/Sheets/Shared Documents/Driver/[A4-4126 - Sheets.xlsx]Trip Expenses Sheet'!$A81, "")

 

Tried to simply concatenate onto the link, but Excel says that there is an error in my formula:

 

A2=IFERROR("https://myDomain.sharepoint.com/sites/Sheets/Shared Documents/Driver/["&A4&"-4126 - Sheets.xlsx]Trip Expenses Sheet"!$A81, "")

 

Any thought/help welcome

5 Replies

@DarrMik 

Try

 

=IFERROR("https://myDomain.sharepoint.com/sites/Sheets/Shared Documents/Driver/["&TEXT(A4,"yyyy-mm-dd")&"-4126 - Sheets.xlsx]Trip Expenses Sheet"!$A81, "")

@Hans Vogelaar 

 

2021-10-14_12-57-31.png

 

Still giving the same error....Excel is highlighting this section when I hit OK:

=IFERROR("https://myDomain.sharepoint.com/sites/Sheets/Shared Documents/Driver/["&TEXT(A4,"yyyy-mm-dd")&"-4126 - Sheets.xlsx]Trip Expenses Sheet"!$A81, "")

 

@DarrMik 

I don't know anything about SharePoint; I hope that someone else can help you.

Not sure that sharepoint is at issue....my original link above works perfectly.
I was just hoping to be able to use a cell's value as the date portion of the link so that I can have the link change based on the value of the date cell (A4)