Referring to a Cell in Another Sheet that Contains an Hyperlink

Art Schneiderheinze

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!

Shaine Greenwood

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.

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

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).

Genevieve P.

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