Calc: Search for string in other workbook, referenced name in cell

[aldowski]

Two workbooks: File1.xlsx and File2.xlsx. In both, using simply just Sheet1 in both.

Workbook File1.xslx has array of cells with some data, example B2 to B30.

Workbook File2.xlsx has array of cells with data, which needs to be checked in File1.xlsx if existing in mentioned array.

The formula in File2.xlsx:
=MATCH(G2,‘file:///C:/Office/Files/File1.xlsx’#$Sheet1.B2:B30,0)
where in G2 is searched string; then position of the array where to search and 0, works good. It returns the position of G2 in File1.xlsx array B2to B30.

What I do need now is to have control of the position where to search, based on the user input (‘file:///C:/Office/Files/File1.xlsx’).

For example, one cell to be input cell where user may put filename (Example: File1.xlsx or File0.xslx), may be G1 cell at the File2.xlsx (Sheet1 in workbooks, where search, match will be performed is uniformed, it has data in B2 to B30).

So, in the G1 there will be user input “File0.xlsx”, for example. Now the tricky part.

The MATCH should take the G1 string and make (possibly) concatenation with other details.

There should be (just as example) something like this:
=MATCH(G2,‘file:///C:/Office/Files/G1’#$Sheet1.B2:B30,0)

Libreoffice is automatically converting the location to URL based one, in this case, URL of the file.

I tried many different options, getting different errors.

How to make the location dynamic, partly based on the content of one cell, where filename is entered?

Thank you.

[anon73440385]

Hello

check function INDIRECT() and use something like:

=MATCH(G2;INDIRECT("'file://" & G1 & "'#$Sheet1.B2:B30");0)
=MATCH(G2;INDIRECT("'file:///C:/Office/Files/" & G1 & "'#$Sheet1.B2:B30");0)
(depends of course on the part, which should be configurable through cell G1)

Ref:. LibreOffice Help - INDIRECT

Explanation: MATCH() requires a reference and INDIRECT() turns a text string into a reference, if the text string describes a valid reference. To get an idea how that works, type =INDIRECT("'file:///C:/Office/Files/" & G1 & "'#$Sheet1.B2:B30") into a cell and finalize using CTRL+SHIFT+ENTER (instead of using ENTER which is what you would do with standard formulas).

[Update - according to additional questions in OPs comment]

1) Requirement referenced file to be open

It is not a requirement the file being referenced needs to be open (here: Files1.xlsx). However for security reason you need to perform the following steps to get that working without any interaction:

• Set `Tools → Options → LibreOffice Calc → General → Section: Update links when opening → Option: [o] Always (from trusted locations)
• Define the location of the referencing(!!!) file as a trusted location in Tools -> Options -> LibreOffice -> Security -> Button: Macro Security -> Tab: Trusted Sources -> Button: Add

2) Relative and Absolute Addressing

If you want G1 be an absolute address, change it to G$1 (e.g use =MATCH(G2;INDIRECT("'file://" & G$1 & "'#$Sheet1.B2:B30");0) and it won’t change if copy down ($G1 fixes the column G but not the row, therefore you get an error since the argument text to INDIRECT() no longer describes a valid reference.). I strongly recommend to read LibreOffice Help - Addresses and References, Absolute and Relative

Hope that helps.

[aldowski]

Thanks for the very helpful answer. This works as it should, as expected. It requires the Files1.xlsx to be opened (which is reasonable). Now, just have to ask if there is a possibility to do the same without the File1.xslx being opened (to make reference on existing, but not opened file). Will the INDEX() be a solution? Or some similar function?

Also, if the cell with this formula should be expanded down, to make the search based on multiple cells, the G2 (what is being searched for), needs to evaluate to G3, G4, G5 to the last cell which content is to be searched for, but G1 should stay same, as there is the name of the Excel file where search is being performed.

=MATCH(G2;INDIRECT("‘file:///C:/Office/Files/" & G1 & "’#$Sheet1.B2:B30");0)

How to (if it is possible) make the G2 dynamic (as it is by default) and the G1 to stay fixed, when expanding the formula cell down ($G1 shows an error)?

[anon73440385]

Please see the [Update] in my answer.

[aldowski]

Thanks a lot for both exact and helpful answers.

First one, related to inter-workbooks references, seems to be great solution for the issue, especially when reference files are being created by the same user, are safe and known.

Second one, I tried to use naming cell, so I named G1 as WorkFileName, then I put the name to the formula instead of cell reference,it works correctly, anyway, I have to say that answer you provided is based on addition of just one single character to formula, which created fixed reference, fast and reliable solution.

Thank you again for specific and exact answers.

[anon73440385]

If you consider your question answered, it would be nice, if you’d click the check mark () next to the answer. Thanks in advance …