Can I use a hyperlink in a formula?

Carsonelli

I have a master sheet (MASTER) that pulls data from many other sheets. On my master sheet, I have a column (Round 1 Sheet) that contains the hyperlink to the specific sheet (Potential Template) where data is being pulled from for that row. The sheets that are being referenced all use the same template, so the fields referenced will be the same on every sub sheet. Currently, I am using the "Link from cell in other sheet" function to pull specific cells from "Potential Template" up to the corresponding fields on the MASTER sheet (see column named 'Round #1 Documentation Strengths'). This linking from cell in other sheet will become quite tedious when there are multiple fields I want to reference AND when I have multiple rows to populate in my MASTER sheet.

I'm wondering if there is a function I can use in the 'Round #1 Documentation Strengths' column on my MASTER sheet where I can reference the 'Round 1 Sheet' column that contains the hyperlink to 'Potential Template', and use an INDEX(MATCH formula to pull the relevant fields into my MASTER sheet. This way I can apply the formula to the entire column rather than individually linking each cell from my sub sheets.

My Master Sheet:

My sub sheet ('Potential Template'):

Genevieve P.

Hi @Carsonelli

Cross sheet formulas can only bring in the display text when it comes to hyperlinked values, as you've found. This means the words of "Potential Template" will pull through, but not the underling URL.

That said, if you had the full URL in the cell this would come through as a clickable link!

Please feel free to add your vote and voice to this Product Idea thread, here.

Cheers,

Genevieve

Carsonelli

Hi Genevieve,

This is even the case when the hyperlink is to another sheet? I guess I can try inputting the URL.

Any advice on what kind of formula I could use to achieve the outcome I'm looking for in my scenario?

Thanks!

Genevieve P.

Hi @Carsonelli

This is true for all hyperlinks that are using a display text with a hidden URL, being brought across sheets via formula. What you could do in this instance is have a cell where the text is above a full URL, like so:

In this case you'll see the URL is clickable! This will be pulled through a formula.

Cheers,

Genevieve