I really need a bit of help with this.
I've a sheet that autopopulates (from a submitted form).
There are currently 5 columns.
| Stamp | Username | Field | Field2 | Field3 |
|---------------|---------------|-------|--------|----------|
|06/03/2015 | [email protected]|Text12 |Blah |INC1126363|
|06/03/2015 | [email protected]|TextABC|Blah2 |INC1128453|
|06/03/2015 | [email protected]|Text123|Blach3 |INC1126270|
However, I also need a 6th colum, which hyperlinks to a URL using the contents in Field3. I'm using the following formula to get the URL
=HYPERLINK(CONCATENATE("https://domain.service-now.com/incident.do?sysparm_query=GOTOnumber%3D",E2,),"Check for Updates")
Which works, however I need the 6th colum to auto populate this. I have tried to use E2:E along with arrayformula (using an IF function). However, using E2:E returns the contents of the entire Field3 column, meaning the URLs aren't returning correctly. They'd come back something like:
https://domain.service-now.com/incident.do?sysparm_query=GOTOnumber%3DINC1126363INC1128453INC1126270
Anyone able to point me in the right direction for this?
give this a try:
=ArrayFormula(if(len(E2:E), HYPERLINK("https://domain.service-now.com/incident.do?sysparm_query=GOTOnumber%3D"&E2:E, "Check for Updates"),))
and see if it works for you ?