string subtitution on a external reference fails?

Genesis1 · December 7, 2020, 2:55pm

I was directed to use INDIRECT in the irc channel, but still not working.

=INDIRECT('"file:///c:/users/no/documents/curatorship/annual report" & A1 & “/debit expenses" & A1 & ".ods"'#REF!.B45)

throws up a 501 error.
this is intended to complete fullpathname & filename with current year (eg 2020-2021) and then fetch value in Cell B45 of that file.

I am a neophyte at post-Cp/M spreadsheets. My experience was Supercalc. Please be patient.

anon73440385 · December 7, 2020, 3:23pm

You seem to build there string for use in function INDIRECT() incorrectly. One tip for development a correct reference string to be used in INDIRECT() chose a helpers cell (development cell) and start with the string only. In your case

`="file:///c:/users/no/documents/curatorship/annual report" & A1 & "/debit expenses" & A1 & ".ods" & "#REF!.B45"`
Now I assume that `#REF!` (which cannot be recovered) is a sheet name (let's call it here `MyRefSheet` then you have:
`="file:///c:/users/no/documents/curatorship/annual report" & A1 & "/debit expenses" & A1 & ".ods" & "#MyRefSheet.B45"`
If that looks like the reference you want to use - put the INDIECT around:
`=INDIRECT("file:///c:/users/no/documents/curatorship/annual report" & A1 & "/debit expenses" & A1 & ".ods" & "#MyRefSheet.B45")`

LeroyG · December 7, 2020, 3:30pm

Since the file name has spaces, ' may to be between the " (one after the first parentheses, another before the hashtag).

If there are space before A1 cell content, add it after report and expenses or in A1.

=INDIRECT("'file:///c:/users/no/documents/curatorship/annual report " & A1 & “/debit expenses " & A1 & ".ods'#Sheet1.B45")

Add Answer is reserved for solutions. Please, click edit below your question if you want to add more information.

Genesis1 · December 7, 2020, 3:57pm

Opaque "Now I assume that #REF! (which cannot be recovered) is a sheet name (let’s call it here MyRefSheet ". #REF! was in original instruction on how to do it in Librecalc help pages.
Your example give a #REF! error
LeyroyG I did have single quotes. I get 501 Error with your verison At this point i don’t know if & A1 & is working… I know =A1 works in text cells.

Genesis1 · December 7, 2020, 4:06pm

everytime i enter it, a closing bracket is added! =INDIRECT("‘file:///c:/users/no/documents/curatorship/annual report " & A1 & “/debit expenses " & A1 & ".ods’#REF!.B45"))<----

anon73440385 · December 7, 2020, 4:07pm

my comment wasn’t meant as a solution to your problem but an instruction on how to develop the correct string to be used in INDIRECT() in the sense of help to help yourself. Anyway - a correct file reference requires ' (quotes) at the end of the filename specification - so at least you need:

=INDIRECT("'file:///c:/users/no/documents/curatorship/annual report" & A1 & "/debit expenses" & A1 & ".ods'" & "#MyRefSheet.B45")

(and may be this is not the correct solution for you as well, since we don’t know the content of A1, how your file and sheet is really named and why A1 appears twice in your construct)

You initial solution had a wrong order of "`' "`": it should be "`" '`" - the single quotes need to be part of the string (the blank is only for demonstration purpose - literally is is "`"'`" )
Finally: It is not unlikely that you get - if everything is correct as far as related to correctly construct the string - an error `Err:540`

LeroyG · December 7, 2020, 4:22pm

@Genesis1, Err:501 is for invalid character; I apologize for not having noticed. There are curved quotes “ before /debit expenses that it are not distinguishable while editing.

Corrected formula:

=INDIRECT("'file:///c:/users/no/documents/curatorship/annual report " & A1 & "/debit expenses " & A1 & ".ods'#Sheet1.B45")

EDIT:

@Genesis1, You posted a comment to this question as an answer at /question/163698/. Please, take a look there, and try to delete the no-answer; if you couln’t, tell us. Thanks.