How can I build a path using data validation variables from another sheet?

[lonelyadmin]

I’m attempting to build a URI using values from several cells…how can I do this?

Sheet1 Cell A1 contains “11aa22bb”
Sheet2 Cell B2 contains “file://\server.domain.com\folder with $\folder with space”
Sheet2 Cell B3 contains “filename_header”
Sheet2 Cell B4 contains “filename_footer”
Sheet3 Cell B5 contains “filename_ext”

On Sheet 1 Cell A2 I would like to generate a full link to the document with a path like “Sheet2.B2,Sheet2.B3,Sheet1.A1,Sheet2.B4,Sheet2.B5”.
Or “file://\server.domain.com\folder with $\folder with space\filename_header-11aa22bb-filename_footer.filename.ext”

I know this looks convoluted, but it’s possible, right?

[Zizi64]

@lonelyadmin

“file://\http://server.domain.com\folder with $\folder with space”

Slash and backslash in a Path or in a URL?

[lonelyadmin]

I did fix that, so now the links are more like file://server/folder%20with%20space$/filename.ext

I could only seem to get this to work by using Insert->Hyperlink, not by using =hyperlink. I’d like to build the path from other cell values. Can’t figure that part out.

[Zizi64]

@lonelyadmin

I could only seem to get this to work by using Insert->Hyperlink, not by using =hyperlink. I’d like to build the path from other cell values.

https://wiki.documentfoundation.org/Documentation/Calc_Functions/HYPERLINK

The two arguments (the URL and the visible/clickable text) are pure strings. You can concatenate them based on the parts located in the cells.

[joshua4]

You can check out this sheet. Notice that I’ve included a very simple URL encoding macro in case at some point you need to handle files with space characters.

Edit: List on other sheet:
FileNamesFromDroplists.ods (26.7 KB)

I think you should just be able to use

[karolus]

whats the point »creating a function« which calls the builtin convertToUrl ??

[mikekaganski]

You can’t use the built-in Basic functions as spreadsheet functions - you have to wrap them in UDFs for that.

@joshua4 since you use the ConvertToUrl, then you could simply use system paths as the source, without manually converting (incompletely) them to URLs (changing backslashes to forward slashes; pre-pending with file://…).

@lonelyadmin Note that HYPERLINK accepts also system file paths directly; so you can even avoid all the conversion to URL altogether, and use it like

=HYPERLINK("\\server.domain.com\folder with $\folder with space" & "\" & "filename_header" & "-" & "11aa22bb" & "-" & "filename_footer" & "." & "filename_ext")

or

=HYPERLINK(Sheet2.B2 & "\" & Sheet2.B3 & "-" & Sheet1.A1 & "-" & Sheet2.B4 & "." & Sheet3.B5)

(given that you change Sheet2.B2 accordingly).

Note that you mentioned that “Sheet3 Cell B5 contains “filename_ext””, but in the expected result, indicated that the URL should end with “filename.ext” - was that a typo, or do you need to replace underscore with dot?

[karolus]

@mikekaganski

You can’t use the built-in Basic functions as spreadsheet functions

yes of course, I completely overlooked that the function is needed as UDF.

[lonelyadmin]

Thanks! This concatenation seems to work best for me. However, I am running in to a separate issue now. The URL I’m building contains the “cell text” value of the HYPERLINK function. I"m not sure if this is possible as it references itself.

Is there a way to reference the “Cell text” of a cell? If I reference the cell itself, I get the hyperlink function that references itself and get the Err:522 code.