Tried concat but seems like it only builds text strings. Trying to modify a link to another file. Any suggestions?
Well, the CONCAT() function returns a string. In what way is this surprising to you? What would you expect?
You should explain in what way you established the “link” you now want to modify. (Linking isn’t quite simple.) If your link is a reference into a different spreadsheet file inside a formula, it is modified like any part of a formula: by editing.
Anyway: Where possible, avoid linking between sheets. It has some disadvantages in every case.
I created an initial “link” to the remote file using a manual cell reference from one sheet to the other by opening the two spreadsheets and selecting via mouse.
I can manually edit the resultant link by changing the file name portion of the path and it works fine.
I am trying to come up with a way to replace only the file name section of the path by using Calc functions of possible so I can do this “on the fly” when needed, rather than by opening the remote file manually.
Have you tried using CONCATENATE(…) within the HYPERLINK(…) function?
=HYPERLINK(CONCATENATE("MyPath"; "/"; "MyFileName"; "#Sheet1.$D$4"))
Was going to but a super helpful guy on hexchat suggested I use INDIRECT and it works perfectly. And he didn’t even find it necessary to be sarcastic to me like someone else here.
But thanks anyway for your helpful suggestion.
Funny but I really try to avoid asking for help on forums like this because way too often those who hang out here feel it’s necessary to make the questioner look smaller than they already feel for not being able to get something working… Not the way things should be, but “it is what it is” I suppose.
Take care.
At the same rate, please pass on the help. Can you edit your answer with an example of what you used in detail enogh for someone else to use it?
1 Like
Good idea Joshua. Will do.
So the problem was: I wanted to create a link to a cell in another spreadsheet or workbook. You can do that manually by opening the two spreadsheets and copy and pasting. But I wanted to look up the file name in a table combine it with a path and essentially “build” a link. Tried to do it with CONCATENATE but what you end up with is a text string. I figured Calc would handle interpretatively, but no go.
Ended up using the INDIRECT function which can assemble a string that Calc can use.
So have at it…