5

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?

1

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);
  }
}

protected by Community Dec 20 '17 at 15:17

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.