Get filename from a cell

shire · March 20, 2022, 3:09pm

hello there I need to get a filename from a cell and use it in a vlookup() function…
something like:
=VLOOKUP(A1;‘file:///home/tolkien/Documents/abf/20032022.ods’#$'REGISTRO '.B5:F53;5;0)
but I need to get “20032022” from a cell input and merge it in the formula.
I can’t find a solution.
thanx

mikekaganski · March 20, 2022, 3:58pm

I’d use INDIRECT.

Villeroy · March 20, 2022, 3:52pm

=VLOOKUP(
    A1;
    DDE(
        "soffice";
        "/home/tolkien/Documents/abf/"&X1;
        "'REGISTRO '.B5:F53"
    );
    5;
    0
)

shire · March 20, 2022, 4:12pm

thanx for answer but I get #N/A.
I tried with indirect() too…

=VLOOKUP(A1;INDIRECT(K10);5;0)

and in cell K10 I put

=CONCATENATE("'file:///home/tolkien/Documents/abf/";L1;".ods'#$'REGISTRO CONAD'.B5:F53")

and in L1 20032022 but I get #REF…
or this

=VLOOKUP(A1;INDIRECT("'file:///home/tolkien/Documents/abf/"& L1 &".ods'#$'REGISTRO CONAD'.B$5:F$53");5;0)

but doesn’t work…

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

Villeroy · March 20, 2022, 4:37pm

#N/A indicates that there is no match while the formula is correct.
I’ve chosen DDE because you have a strange sheet name with space. With 'file:/// references you have to avoid any strange characters in the path, file name and sheet address.

erAck · March 20, 2022, 5:05pm

That’s not true, both

='file:///home/you/file name.ods'#$'Sheet Name'.A1

and

=INDIRECT("'file:///home/you/file name.ods'#$'Sheet Name'.A1")

work perfectly fine.
Percent-escaped URL path works as well, like

=INDIRECT("'file:///home/you/file%20name.ods'#$'Sheet Name'.A1")

(though not in the reference URL fragment).

shire · March 20, 2022, 5:22pm

the filename in 20032022.ods…no space in it.
I need to find the value inside cell A1 (which is in a different sheet, in a different file) in a range of cells (b5.f53) in a sheet called REGISTRO CONAD inside 20032022.ods file and if it find it, it returns the value in the 5 col.
=VLOOKUP(A1;‘file:///home/tolkien/Documents/abf/20032022.ods’#$‘REGISTRO CONAD’.B5:F53;5;0) this works of course.
But I need to change the filename getting it from a cell, I have more file name called
19032022.ods, 21032022.ods etc…so if you don’t find the value in one file, you change the name (only 20032022 ) in the cell and it search in another file…
hoping to be clear…
thanx

erAck · March 20, 2022, 6:02pm

If you get #REF! error from INDIRECT() with your example then make sure that the reference syntax for string references matches your input (or vice versa), i.e. with this Sheet.A1:B3 notation the Calc A1 syntax is expected, Excel A1 would be Sheet!A1:B3. See Tools → Options → Calc → Formula, Detailed Calculation Settings and online help. If that is Default settings then it follows the Formula syntax (on same tab), else inspect Custom Details,

shire · March 20, 2022, 6:24pm

Thanx for your hint, I solved!