Ask Your Question
0

How to refer range of cells from other libreoffice excelsheet

asked 2016-09-23 08:03:30 +0100

Arthy gravatar image

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

edit retag flag offensive close merge delete

Comments

Your Operating System / version of it?
Your LibO version?
For what reason not using native .ods?

Lupp gravatar imageLupp ( 2016-09-23 11:05:32 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2016-09-23 22:30:04 +0100

erAck gravatar image

updated 2016-09-23 22:49:48 +0100

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).

edit flag offensive delete link more
0

answered 2016-09-23 21:28:39 +0100

mark_t gravatar image

updated 2016-09-24 07:46:32 +0100

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.

edit flag offensive delete link more

Comments

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.

erAck gravatar imageerAck ( 2016-09-23 22:24:07 +0100 )edit

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.

mark_t gravatar imagemark_t ( 2016-09-23 23:14:19 +0100 )edit

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.

mark_t gravatar imagemark_t ( 2016-09-23 23:16:11 +0100 )edit

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.

mark_t gravatar imagemark_t ( 2016-09-23 23:26:31 +0100 )edit

Link between xlsx documents on Linux Mint with LibreOffice 5.1.4.2 seems to work OK with LibreOffice link syntax

mark_t gravatar imagemark_t ( 2016-09-24 00:20:09 +0100 )edit

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.

mark_t gravatar imagemark_t ( 2016-09-24 07:20:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-23 08:03:30 +0100

Seen: 398 times

Last updated: Sep 24 '16