Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

6
  • 1
    Actually, the hyperlink line can just be changed to =HYPERLINK(CONCAT(RC[1],RC[2]),RC[3]). If you prefer the letter column style excel will automatically change it. However, in the R1C1 style (columns are numbered) the formula is the same for every row so that makes it a lot less clunky. Commented May 5, 2020 at 5:33
  • I must have updated my script right when you posted this. However, yours is so much cleaner! Thank you! Commented May 5, 2020 at 5:44
  • Is there a way to execute this after a refresh All is clicked/completed on a datasource? That was also part of my edit/update. Thanks again! Commented May 5, 2020 at 5:45
  • 1
    Not sure how your update occurs but usually the way to add a function that executes on change is to add the sub "Private Sub Worksheet_SelectionChange(ByVal Target as Range)" to you worksheet-> Go to your VB window, double click on your "Query" worksheet and select worksheet and selectionChange, that should create it. Paste or call your code from here and it should trigger whenever there's a change in the worksheet. Commented May 5, 2020 at 22:00
  • 1
    If you want to limit the range that triggers your code insert an if statement on the target variable. For example if you want it to only run if there's a change in column A: if Target.column = 1 then InsertHyperlinkFormulaInCell(). Commented May 5, 2020 at 22:00