Automatic hyperlink creation not working?

[yeevy]

I’ve tried automating the creation of hyperlinks (eg. =HYPERLINK(“#’Sheet1’!A2”;1) ) to other worksheets by using a TEXTJOIN function. Then, if I copy and paste just the values of the results, I get the exact hyperlink formulas I need, but they don’t become clickable and instead just display the formula until I double click on them (to enter edit cell mode) and then click away from it, as if that action updated it on some way. I need to generate several hundred hyperlinks, so I would prefer not having to click on each one of them for them to work, so how could I automatically make them all clickable?
Also, a similar thing happens when typing or pasting a link directly into a cell, where it wouldn’t become clickable until I add a space at the end, how to change that?

 

[yeevy]

I’ve tried automating the creation of hyperlinks (eg. =HYPERLINK(“#’Sheet1’!A2”;1) ) to other worksheets by using a TEXTJOIN function. Then, if I copy and paste just the values of the results, I get the exact hyperlink formulas I need, but they don’t become clickable and instead just display the formula until I double click on them (to enter edit cell mode) and then click away from it, as if that action updated it on some way. I need to generate several hundred hyperlinks, so I would prefer not having to click on each one of them for them to work, so how could I automatically make them all clickable?
Also, a similar thing happens when typing or pasting a link directly into a cell, where it wouldn’t become clickable until I add a space at the end, how to change that?

Ok, after searching around the Internet a bit more, I found a solution, which is simply to replace any character common to all Hyperlink formulas with itself using the find and replace function.
Of course, if you have other solutions, you can share them here.

 

[smozgur]

When I need to do the same thing for lots of cells, and find myself in the same situation, I simply select the range, go to VBE and open the Immediate Window, and write and execute the following one liner to create links quickly:

for each cll in selection.cells: cll.hyperlinks.add cll, cll.value: next cll