1

Here's a sample form:
https://docs.google.com/forms/d/e/1FAIpQLSfiPSHacKKNEfe-Jq82DBScbCBRkwtm8rq2Jq7y-ceLgGIC3Q/viewform

And here's the linked spreadsheet:
https://docs.google.com/spreadsheets/d/16mNc5EBNRBZ_E3jWa848NhFUNoTu496_lBSjb4Rd7x0/edit

I've created an extra sheet named Comments to draw data from the Source sheet. The data on the second sheet should be exactly like the first sheet, except column B of the Comments sheet: I'd like the usernames to be hyperlinks using the following URL:
https://t.me/username
As a result, for the username @johnDoe on sheet 1, I should have the following on sheet 2:
@johnDoe

2
+150

You can do this very easily with a literal array and array formula:

={Source!A1:C1;Source!A2:A,arrayformula(if(istext(Source!B2:B),Hyperlink("https://t.me/"&substitute(Source!B2:B,"@",""),Source!B2:B),)),Source!C2:C}

The first part creates the header, the below it it pulls in the first column, then uses and if check to only hyperlink fields in column B that have text in it, then pulls column C.

This way you only enter the formula once at the top and never have to edit it again

enter image description here

  • I inserted the above formula in the Comments sheet A1 with no changes, but I got an error message. I don't know why, but it doesn't work for me! – Mori Jul 18 '17 at 8:07
  • Thanks for the answer! It does exactly what I want. I just wish there were a simpler formula to handle it. – Mori Jul 18 '17 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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