Hi
I need to create a reference to another file that will work under Excel. The file can be saved anywhere, so the path cannot look like this:
=‘file:///Path & File Name’#$‘SheetName’.CellReference
I tried with:
=[SourceWorkbook.xlsx]Sheet!$A$1
But it doesn’t work
And it’s not about the value from the cell only, but about nesting it in the formula.
=VLOOKUP(C21;$Sheet1.$A$2:$E$4;3;FALSE())
And where Sheet1 is, there should be a reference to another file.
Calc 7.3.2.2
Install Excel or use LibreOffice with its native ods file format.
The magic happens in Calc conversion to xlsx , you don’t need to manually change anything.
If I enter ='file:///H:/AskLibreOffice/External.xlsx'#$Sheet1.B2 in a Calc spreadsheet and save it as LookingUp.xlsx then when I open it in Excel 2010 and allow updating the cell contains ='H:\AskLibreOffice\[External.xlsx]Sheet1'!B2 and gives the correct number, the cell can be copied down to display more data.
If I reopen it in Calc and then add another column with =vlookup(A2;'file:///H:/AskLibreOffice/External.xlsx'#$Sheet1.A1:B16;2;0) then when I open it in Excel that same cell contains =VLOOKUP(A2,'H:\AskLibreOffice\[External.xlsx]Sheet1'!A1:B16,2,0) and displays the correct number, it can be copied down to shown more data.
If it doesn’t work for you then possibly updating data in the lookup spreadsheet is blocked in Excel or maybe you need to add the location of the external spreadsheet as a trusted location in Excel.
Hi @EarnestAl Thank you for your reply.
I went to a friend who has Excel and indeed - magic happens. The formulas have been redone and they work. Thank you 
@Villeroy - why are you wasting your time answering like this?
Because it is such a common error to not work in the application’s native file format.
Whatever you do with whatever file generating application, always save your work in its native file format and export separate copies to foreign file formats when such a copy is needed. This way your working copy is guaranteed to include all features that may get lost in a foreign file format.
Exchanging foreign file formats via email:
The same could be done from the view point of the Excel user where native format would be xlsx and the foreign format would be ods. However, LibreOffice handles Excel documents way better than Excel handles Calc documents, therefore your exchange format might be xlsx in most cases.
P.S. If you exchange stuff through the referenced file without changing the referencing file, you may work with ods on your system and with xlsx on the other system and exchange raw data with a linked file in either format. Raw data in foreign format should not impose any problem on either application and both sides can work with their respective reference syntax.
Also Save URLs relative to * settings, in Options → Load/Save → General, may affect how the references get stored - i.e., if the end result in Excel will be ='H:\AskLibreOffice\[External.xlsx]Sheet1'!B2 or ='[External.xlsx]Sheet1'!B2.
[Unrelated]
I can feel @Villeroy’s pain. However, in this question, I saw no evidence that OP does that common error. The question did not communicate the intention to work with external format as the primary format - even if it could happen to be the case. 
Now your statement takes on a completely different meaning.
When the files are only for me, I use odf. Unfortunately, I often work with shared files and then I have no choice - I have to adapt to xlsx.
You write about export, but I only see xml there. So you meant the save option here, or is there actually some particular export option that I missed?
Simple Save is meant. There’s an ambiguity in terminology, when “export” might mean a specific menu item, or any process of using export filters (which is used even for ODF).