Referring to a Cell in Another Sheet that Contains an Hyperlink

Art Schneiderheinze
Art Schneiderheinze ✭✭
edited 12/09/19 in Formulas and Functions

I am wanting to refer to a cell (or find a match in a range of cells) in Sheet 1. The cell in Sheet 1 contains a hyperlink.

What I get in Sheet 2, however, is just the text in the cell, not the text as a hyperlink.

Sheet 1 (contents of cell, text as hyperlink):

BUS 123 6Wk Online

What I get in Sheet 2:(text but no hyperlink)

BUS 123 6Wk Online

One of the formulas I tested in Sheet 2:

=LEFT({Course Master Record Range 1}, LEN({Course Master Record Range 1}))

I get the text, but the hyperlink doesn't "come with it".

 

It doesn't seem to matter what formula I use, they are reference the same cell (or range), and all I get is the text, not the hyperlinks.

Thanks!

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hello—

    There isn't a way to preserve the hyperlink when referencing cells that contain a hyperlink with display text.

    You'd need to have the hyperlink without the display text in a cell. Here's a potential workaround for this:

    1. Create a new column
    2. Copy and paste all of your hyperlinked cells to the new column
    3. Right-click the cells in the new column and select Hyperlink, then delete the text (you'll have to do this to each individual cell
    4. Use a VLOOKUP function to return the hyperlink when the display text is found on that cell (more on VLOOKUP here)

    This will require that you have a "hyperlink" column in each of your sheets.

    · Share on FacebookShare on Twitter
  • Erik Beltran
    Erik Beltran ✭✭✭

    Shaine,

    Can you shed some light if instead of an external hyperlink i am pointing to a Smartsheet item?

    Would i need to get the actual URL of the Smartsheet item and then do the workaround?

     

    Thanks,

    Erik

    · Share on FacebookShare on Twitter
  • Audrey512
    Audrey512 ✭✭

    Shaine - I have a similar issue but not with display text. I have the full URL in my cells, but any time I try to reference that cell (whether VLOOKUP, or =[cell] etc.. ) only a portion of the URL is hyperlinked, so in the referenced cell, the hyperlink does not work (since it is not the full url that the browser is trying to find).

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Audrey512

    Can you post a screen capture of what you're seeing (but block out sensitive data)?

    As long as the full URL is displayed in the original cell and is clickable in that cell, a formula or cell-link referencing that cell should pull the full URL over as well.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!