Hyperlinks Not Functioning Properly

English
,
#1

Hi, I am using Calc to open files on my HDD. I have a large amount of files which can be displayed in a single cell using vlookup.

Here is what the formula looks like:

=HYPERLINK(VLOOKUP((CONCATENATE($B$3;" ";$B$5;" ";$B$4;" ";$B$6;" ";$B$7));$SRP.F3:H8231;3;0);"File")

The hyperlink works, until I change one of the vlookup cell references (B3-B7), to display a different file. I can fix it by deleting and retyping any character in the formula, but I don’t want to have to keep doing that.

To be clear, the files are opened within another software, and clicking the hyperlink opens them correctly, and if I display the file paths on the sheet where they are indexed as hyperlinks, each link works every time.

I have a version of this sheet working in excel and this issue does not occur, but I would rather use Calc because of other issues I have with excel

I have tried CTRL+Click on and off, I have tried low macro security level and URL recognition is turned on.

I’ve spent hours searching the internet for similar issues and I’m coming up empty.

Any help would be greatly appreciated, thanks.

[erAck: edited to codify using ```, see This is the guide - How to use the Ask site? - #6 by erAck ]

#2
  1. What’s your version of LibreOffice?
  2. What happens if you do not edit the formula after a change in the source cells, but force recalculation by Ctrl+Shift+F9 ?
  3. Is >Data>Calculate>AutoCalculate enabled?
  4. You may also remove the ;"file"part completely to get shown what URL actually is currently present in the HYPERLINK() cell…
#3

Thanks for your comment.

  1. Version is 4.1.11
  2. Nothing happens.
  3. There is no such path in my menu.
  4. This is intentional, to make the result look cleaner
#4

  1. are you using LibreOffice or OpenOffice? Your Version is either an old LibreOffice or (more likely) OpenOffice

  2. (must include this, because the site will auto-correct my numbering)

  3. May be clear, if answer for 1) is OpenOffice

  4. You may put “file” in again after you found the error, but if you prefer a more difficult way … (Empty grids are looking very clean and usually contain no errors…)

#5

No, there was never a LibreOffice version 4.1.11 - the last one in 4.1 branch was 4.1.6.

You could put the numbers differently: e.g., enclose in backticks, or use a dash after the number instead of dot, or otherwise prevent the automatic format :slight_smile:

#6

I was indeed using OpenOffice :man_facepalming:

Edit: The Issue still seems to be there with Libre. The links worked when I opened the document for the first time, just like they do on creation, but as soon as I edit the source cells, the links break. As with OpenOffice, if I edit the formula it works.

#7

What is the result of the following formula?

=VLOOKUP((CONCATENATE($B$3;" “;$B$5;” “;$B$4;” “;$B$6;” ";$B$7));$SRP.F3:H8231;3;0)
#8

Just a note, that originally OP typed

=HYPERLINK(VLOOKUP((CONCATENATE($B$3;" ";$B$5;" ";$B$4;" ";$B$6;" ";$B$7));$SRP.F3:H8231;3;0);"File")

and it’s just the Discourse engine (and not using the source code formatting, like enclosing with backticks), that made the quotations look that odd.

But still - the whole issue is still completely missing the single important thing - a sample ODS file.

FTR: I couldn’t repro using this sample document, that contains the original formula and its parts in A1, B1, and A2, and some values in the referenced cells, using Version: 7.3.1.1 (x64) / LibreOffice Community
Build ID: 349cd3ad57dce98d6b54b76f8e9f456ac7d7edb7
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-US (ru_RU); UI: en-US
Calc: CL.

vlookup_hyperlink.ods (10.5 KB)

#9

I have managed to reproduce the fault with a simple version of my sheet. Thanks again for your help
example.ods (11.3 KB)

#10

Aha, so the problem is using Validity list to change dependency. I’d suggest to file a bug. @erAck might know if there already is such a bug, but I’d suggest to file one anyway, without spending time searching for duplicates.

#11

I’m not aware of such thing.
@Dicey: please file a bug and attach the sample document there.

#12

Meanwhile the suggested workarouind might do.
exampleWorkaround.ods (12.1 KB)

1 Like
#13

I’ll do that now @erAck . Thanks for the help everyone. For now I’ll use the workaround @Lupp suggested, which seems to be working.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.