Google-sheets – Dropdown lists with hyperlinks

google sheetslinks

I'm trying to set up sheet1 with a dropdown list.

Data validation, cell range: Sheet1!B16  
Criteria: List from a range Sheet2!A:A
Show dropdown list in cell
On invalid data show warning.

Data on sheet 2 is a list of locations as below
=HYPERLINK("https://www.google.com/maps/.....", "Friendly Name")

My problem is it only brings across the Friendly Name & not the Link.

How can I bring the link across?

Best Answer

Short answer

At this time data validation only return plain text values but you could use a script to replace the value with a formula including the HYPERLINK function.

Explanation

Data validation only returns as dropdown options, the plain text values, it's not able to offer a formula nor a rich text content. The alternative is to use Google Apps Script to replace the assigned value with the corresponding formula. For general instructions on how a script could be added to a Google spreadsheet see Extending Google Sheets.

Script

This script is a simple on edit trigger. This will check if the edited cell is the cell to validate an if this is true, then will replace the selected value with the corresponding formula of the criteria range.

function onEdit(e) {
  // Cell to validate
  var cellAddress = 'B16';
  var sheetName = 'Sheet1';
  // Current cell
  var eA1Notation = e.range.getA1Notation();
  var eSheetName = e.range.getSheet().getName();
  // If the edited cell is the cell to validate set the formula
  // otherwise do nothing
  if(eA1Notation == cellAddress && eSheetName == sheetName ){
    //Criteria
    var criteriaSheet = 'Sheet2';
    var criteriaColumn = 'A:A';
    var validationRange = e.source.getRange(criteriaSheet + '!' + criteriaColumn);
    var values = [].concat.apply([], validationRange.getValues());
    var row = values.indexOf(e.range.getValue());
    var formula = validationRange.getFormulas()[row];
    e.range.setValue(formula);
  }
}
Related Topic