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.