In Google Sheets, Is there a way to use hyperlinks in a data validated range?

Question

In Google Sheets, a cell using a data validated range consisting of hyperlinks will only display the link text and is not an actual link.

Example, I have a google spreadsheet consisting of 2 sheets. Sheet 1 has a cell with data validation getting it's values from a range on sheet 2, like so Sheet2!B2:B50

Sheet 2 cell B2 contains a hyperlink like this:

=hyperlink("https://docs.google.com", "LINK TEXT")

When you select that item back in Sheet 1 in the validated cell, the cell only displays LINK TEXT and is not a hyperlink.

Is there a way to use hyperlinks in a data validated range?

Answer 1

I think, there's still no direct way to do this since you'd asked the question. But it's possible in two ways:

1. make OnEdit script and replace selected text with
   corresponding link
2. make 2 separate columns and emulate choise.

I want to suggest the second way as it's easier to implement.

The result will look like this:

Here's the link to my test file.

---

Step 1. Prepare data

So Sheet2 contains data, it should look like this:

Separate:

• links in column B
• and their labels in column A.

Step 2. Make Data Validation

Go to Sheet1. And in required range make the new rule for Data Validation. In our sample it's column A. So select range > Go to Data > Validation... > select range Sheet2!A2:A100 from your data sheet.

Note that in Google Sheets you may use bigger range for validation, sheets would ignore blanks.

Also there's a good reason to make the result of selection invisible. To make it, select custom number format and use this text:

;;;

Step 2. Make Hyperlinks with formula

In Sheet1, cell B2 paste the formula:

=ArrayFormula(if(A2:A<>"",HYPERLINK(VLOOKUP(A2:A,Sheet2!A:B,2,0),A2:A),""))

It will expand automatically.

That's all!