Referring to a Cell in Another Sheet that Contains an Hyperlink

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!

Find more posts tagged with

Sheets

Formulas

Cell linking

Comments

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:

Create a new column
Copy and paste all of your hyperlinked cells to the new column
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
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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?