Hi,
I would like to refer a range of cells to draw a chart, for example from A1 to A5 of calc1.xlsx in other libreoffice calc excel sheet calc2.xlsx. Could you please help me to solve this.
I tried with, ‘file:////calc1.xlsx’#$sheet1.$A$1:$A$5. But didnt work out.
Thanks,
Arthy
Your Operating System / version of it?
Your LibO version?
For what reason not using native .ods?
Edited after reset of my user profile to resolve issues with links to data in cells of a different worksheet, see comments below. I’m now able to link the data without using the INDIRECT function and this now works as described by @erAck.
I think you would need to use cells in calc2.xlsx to link to calc1.xlsx and then plot the data from calc2 cell range.
Creating a chart in Excel with data from a different worksheet is possible, but if this is then opened in LibreOffice the chart range is replaced with an embedded Data Table.
Note using LibreOffice 5.2.1.2 x64 on Windows 8.1.
Using workarounds with INDIRECT is not necessary. What you see in Excel for the first example is the Excel syntax, if you save that as .xlsx and import in LibreOffice you’ll see the LibreOffice syntax. The same syntax can be used for new external references.
Excel syntax was “=[calc1.xlsx]Sheet1!$A$1”. Then opening the xlsx with LibreOffice this changed to “=[‘file:///D:/UserData/Mark/Desktop/Calc/calc1.xlsx’]Sheet1!$A$1”. Trying to edit this or create in LO by selecting the cell in calc1.xlsx for calc2.xlsx was changing this to “=[‘file:///d:/userdata/mark/desktop/calc/calc1.xlsx’]sheet1!a1” which gives Err:507.
I tried typing the syntax from your answer “=‘file:///D:/UserData/Mark/Desktop/Calc/calc1.xlsx’#$Sheet1.$A$1” but this is changed to “=‘file:///d:/userdata/mark/desktop/calc/calc1.xlsx’#$sheet1.$a$1” and gives #NAME? error.
Also tried with .ods files, but still have the same problems. Creating a link to another ods file still uses the excel format. Maybe this is a windows or LO 5.2.0.4 issue, I’ll try updating LO.
Link between xlsx documents on Linux Mint with LibreOffice 5.1.4.2 seems to work OK with LibreOffice link syntax
Found Bug101691, which had a link to Bug97185, which described a problem with links that was fixed by reset of user profile. I’ve reset my user profile and now the linking for cell values between xlsx worksheet documents is working in Windows 8.1 with LibreOffice 5.2.1.2 without the INDIRECT function. I still seem to need helper cells to link data for use in a chart.
file:////calc1.xlsx likely is not the correct URL to your file’s location, this example assumes that the file calc1.xlsx resides in the root directory, which likely it does not. If it is in your LibreOffice document folder then try to enter the formula with ‘calc1.xlsx’#$Sheet1.$A$1:$A$5 and the correct full path will be displayed afterwards. If the file resides somewhere else then use the full path URL, e.g. 'file:///home/you/path/calc1.xlsx'#$Sheet1.$A$1:$A$5
If you tried to directly enter the external reference into the chart data range dialog that does not work, you’ll have to fill cells in the local document first, for example by entering ='file:///home/you/path/calc1.xlsx'#$Sheet1.$A1 in one cell and then pulling down the formula 4 more rows, or entering ='file:///home/you/path/calc1.xlsx'#$Sheet1.$A$1:$A$5 as array formula (Shift+Ctrl+Enter).