I have a spreadsheet that I use to catalog photographs. I have attached a sample Calc sheet for clarification:
Create a Hyperlink.ods (116.0 KB)
.
On the “Data” sheet (column C), I use the following formula to create a hyperlink to display photos that are stored on my computer:
=HYPERLINK(CONCATENATE(AC2,"\",AD2,"\",AE2),"Link")
Each row contains the information for one photograph.
.
Also on the “Data” sheet (column AF), I use a formula that counts the number of matches to a name that I’ve selected on a separate “FindPerson” sheet.
.
On the “FindPerson” sheet, I use the following Index/Match formula to display the list of all rows that match the selected name.
=IF(ROWS($2:2)>MAX($Data.$AZ:$Data.$AZ),"",INDEX($Data.B:$Data.B,MATCH(ROWS($2:2),$Data.$AZ:$Data.$AZ,0)))
.
This method works great EXCEPT the displayed result of the Link (in row C of the “FindPerson” sheet is not hyperlinked… it just displays the word “Link.” I would really like to be able to click on a link to open these photos from the “FindPerson” sheet… is there any way to display these matching results with the actual clickable hyperlink from the “Data” sheet?
.
Thanks for any assistance!
I wouldn’t comment on the connection between the different sheets, but for me even a link created the incomplete way and using the “wrong” path delimiter “\” like in
=HYPERLINK("C:\Users\Myself\Documents\images\something.jpeg"; "sometext")
works in LibO 7.5.3.2 under Win 10 if the file exists, and a software for opening .jpeg files is associated under Win.
Thank you, @Lupp.
But the Link is pulled from the data sheet via a formula:
=IF(ROWS($2:2)>MAX($Data.$AF:$Data.$AF),"",INDEX($Data.C:$Data.C,MATCH(ROWS($2:2),$Data.$AF:$Data.$AF,0)))
.
I tried encasing the above formula inside the Hyperlink formula, but it did not work:
.
=Hyperlink(IF(ROWS($2:2)>MAX($Data.$AF:$Data.$AF),"",INDEX($Data.C:$Data.C,MATCH(ROWS($2:2),$Data.$AF:$Data.$AF,0))))
.
I’d love to find a way to tweak this formula or format the column to turn the result into a hyperlink.
You can’t “turn” an arbitrary string into a hyperlink.
The string expression needs to return the correct filepath of the image file. The second argument of a HYPERLINK() expression (if any) is an ordinary string which is returned to the cell for the display. The called link is defined by the first argument which can’ be retrieved by a referencing formula.
If you use HYPERLINK() expressions you should omit the second argument during development to better see the relevant things. Also the result shown then can not be referenced as a working link. You need to use HYPERLINK() in the cell where you want to be able to call the link from,
I’ve figured out a work-around that seems to work well. Instead of trying to pull the existing hyperlink from the “Data” sheet via the Index/Match search method, I created a new column on the “FindPerson” search sheet that concatenates the 3 columns AFTER they are pulled from the “Data” sheet via the Index/Match formula.
.
=HYPERLINK(CONCATENATE(Q5,"\",R5,"\",S5),"Link")
.
However, this formula displays the word “Link” all the way down the sheet, even if there are only a few matching results. Is there some way to display the word “Link” for a hyperlink ONLY if the text value in cell Q5 is NOT blank?
You simply can’t. The hyperlink functionality provided with the help of the HYPERLINK() function is only available in a view for the UserInterface. It is in no way assigned to a cell or its content. (It can’t even be retrieved by user code based on the API.)
I didn’t thoroughly study your formulas insofar, but suggested different solutions. A HYPERLINK() expression always returns the result for the second argument, if present.
Our ways of thinking don’t seem to be fully compatible, but I hope you can better adapt to my ways rhan I can the other way. I may simply be too old.
E.g. I’m not a friend of empty (intermediary) columns and the like, and I very rarely use the DataValidity tool (which I regard somehow misleading) and next to never “merged cells” or “pretty tables”.
For the attached example based on your attachment I chose -just for fun- the longest filename of my life. But I couldn’t overcome myself to include spaces. I guess I still think spaces in names are amoral.
See attachment:
disask93115EmilyMeHasLotsOfDocumentsAndWantsToLinkToThem.ods (101.1 KB)
In the second sheet you need to click the little “+” to get the helper columns shown which contain the relevant formulas.
THANK YOU so much, @Lupp , for the Demo example. I will need to take some time and review your formulas to see if I can figure out how they work.
.
I admit that part of MY problem is age… hard to teach an old dog new tricks. Ha! Ha!
.
I’m curious as to how you got the “+” sign to appear above the helper columns.
I used >Data>Group and Outline>Group>>Columns.
And (somehow OT?) you may be interested in this “essay” about names.
In addition: Filtering is mostly better done by the filter tools than by formulas. Helper columns containing preparing formulas may be needed, however.