Re: [libreoffice-users] Calc 5.x Mac - syntax to link cells in two spreadsheets?

Hi.
This is not some sort of auto spell correct on the Mac where LO changes 
all the upper case letters to lower case, changes some of the characters 
ie. #, $ and ! are removed is it.
steve

On 31/10/17 11:36, bunk3m wrote:
> OK I've had some time to test this with the same spreadsheets on 
> various versions of LO on Mac, LO on VM running Win & LO on VM running 
> Linux (Xubuntu).
>
> I've tested LO 5.1, 5.3 & 5.4 on Mac, LO 5.1 Win, LO 3.5.7.2 Linux and 
> OO 3.3 and OO 4.1
>
> In each case I followed these steps.
> 1) open two linked spreadsheets
> 2) place "=" in a cell, switch to other spreadsheet, select the linked 
> cell and press enter key.
> 3) in each case on the Mac (LO 5.1/5.3/5.4); LO changes all the upper 
> case letters to lower case, changes some of the characters ie. #, $ 
> and ! are removed and you get an error message.
>
> If you try to manually change the text entry changes from (3), when 
> you press the enter key it reverts to the incorrect text.
>
> Using the same spreadsheets and following steps (1) & (2) and using 
> Win LO 5.2 & Linux Xubuntu LO 3.5.7.2, the linking works without error.
>
> Using the same spreadsheets and following steps (1) & (2) and using 
> Mac OO 3.3 and 4.1, the links work without error.
>
> So at this point I opened the spreadsheet in LO Mac and the links 
> created by Win and Linux versions are still OK.
>
> If I use Mac LO to copy and paste the entire cell created by Win & 
> Linux versions of LO and OO,there is no transformation and the linking 
> works.
>
> If I use Mac LO and select in the cell, highlight and copy the link 
> formula, and paste the formula into another cell, Mac LO transposes 
> the formula and ERROR.
>
> I think we've found a bug.
>
> B.
>
> On 30.10.2017 14:46, bunk3m wrote:
> > A quick update.
> >
> > I wasn't able to find LO 5.1 but I did find LO 5.3 and OpenOffice 
> > 3.3. I downloaded and installed both LO 5.3 and OO 3.3.  LO 5.3 has 
> > the same issue and won't allow the link.
> >
> > OO 3.3 has absolutely no problem with the linking to external files.  
> > So my file does link when using OO.  This is beginning to look like a 
> > bug in LO 5.3 & 5.4.
> >
> > I will try OO 4.x and see if there it work also.
> >
> > And I will see if I have LO on a Linux VM image to see if the 
> > spreadsheet will work using Linux.
> >
> > B.
> >
> > On 30.10.2017 14:04, bunk3m wrote:
> > > Hi Steve,
> > >
> > > Thanks for your continued help.
> > >
> > > I checked my LO and it has Formula syntax -> Excel A1.  I tried 
> > > again to change the syntax "!" vs ".", "#" or not etc. but still 
> > > getting ERR:510|ERR:507 or ERR:501 and the occasional #NAME?.  :-p
> > >
> > > Please let me know if you have a problem when you try LO 5.4 Mac.  
> > > If it's a bug, then I'll happily add it.
> > >
> > > I will see if I can find LO 5.1 Mac and give that a try.  Will 
> > > report back.
> > >
> > > B.
> > >
> > > On 29.10.2017 21:57, Steve Edmonds wrote:
> > > > Hi.
> > > > My Mac has LO 5.1..., My Linux 5.3...
> > > > When I add the connection on my Mac as in my earlier test I get a 
> > > > formula like
> > > > ='file:///mnt/public/Documents/Global/List of CDs.ods'#$Sheet1.C11
> > > >
> > > > I don't get any error, it calculates fine on creation of the 
> > > > formula, but cannot get it to update if the source changes.
> > > >
> > > > I will update my Mac to 5.4 and see if that changes anything, but I 
> > > > would assume if the file is an ods then adding a new formula should 
> > > > result in ods formatting.
> > > > BUT from a few years ago I found
> > > >
> > > > "I think the issue is the separator between sheet name and address 
> > > > "!",
> > > >
> > > > First option)
> > > > Change Menu/Tools/Options/LibreOffice/Formula/Formula 
> > > > options/Formula syntax -> Excel A1."
> > > >
> > > > So may be check Menu/Tools/Options/LibreOffice/Formula/Formula 
> > > > options/Formula syntax
> > > >
> > > > Steve
> > > >
> > > > On 30/10/17 09:54, bunk3m wrote:
> > > > > Thank you for testing this, Steve.
> > > > >
> > > > > I tried what you suggested near the beginning of my testing and 
> > > > > tried it again just now.  When I follow your instructions, I get 
> > > > > an ERR:507.
> > > > >
> > > > > I've tried following the instructions that Remy provided [The link 
> > > > > was inserted as per the instructions in the help 
> > > > > (https://help.libreoffice.org/Calc/Referencing_a_Cell_in_Another_Document)] 
> > > > > but I get completely different looking formula.  There is no 
> > > > > "green check mark" as per the help instructions either.
> > > > >
> > > > > Within the same spreadsheet I get similar, ie. ='tab'!cell  This 
> > > > > does work.
> > > > >
> > > > > To reference a spreadsheet outside of the present spreadsheet,
> > > > >
> > > > > where Remy gets:
> > > > > ='file:///mnt/public/Documents/Global/List of CDs.ods'#$Sheet1.C11
> > > > > on Linux
> > > > >
> > > > > I get:
> > > > > =['file:///users/me/folder/spreadsheet.ods']'tab'!i54
> > > > >
> > > > > This isn't what the help file shows either.
> > > > >
> > > > > Could this be something due to the fact that this started out as 
> > > > > an Excel file but was saved as an ods sometime over the past months?
> > > > >
> > > > > I'm confused.
> > > > > B.
> > > > >
> > > > > On 29.10.2017 16:12, Steve Edmonds wrote:
> > > > > > Just tried on my mac.
> > > > > > Open the sheet I want to perform the calculation in.
> > > > > > Open the source sheet from which I want to fetch the data
> > > > > >
> > > > > > Enter the formula in a cell in the first sheet, i.e =2*
> > > > > > then click in the source cell in the second sheet,
> > > > > > then click enter and it seems to work.
> > > > > > steve
> > > > > >
> > > > > > On 30/10/17 07:42, bunk3m wrote:
> > > > > > > Hello all,
> > > > > > >
> > > > > > > I've been struggling with this one for a week now. Google hasn't 
> > > > > > > been my friend so far so thought I'd ask the user group.
> > > > > > >
> > > > > > > LO 5.4.2.2 Mac 64 Bit.
> > > > > > >
> > > > > > > I have a spreadsheet (originally in xls but now ods) where data 
> > > > > > > is provided to a summary spreadsheet.
> > > > > > >
> > > > > > > I am unable to get the syntax to link the cells in the two 
> > > > > > > spreadsheets on my Mac.
> > > > > > >
> > > > > > > I've tried, single and double quotes for file location, "." or 
> > > > > > > "!" for identifying the source cell but nothing seems to work.  
> > > > > > > I get ERR:507/ERR:508/ERR:501/ERR:510 and occasionally #NAME?#
> > > > > > >
> > > > > > > The file names have spaces as do the tab in the spreadsheet.
> > > > > > >
> > > > > > > I've tried the =HYPERLINK(file:///... and ='file:///...' 
> > > > > > > commands but that doesn't work.
> > > > > > >
> > > > > > > I've also tried the Insert Hyperlink but that won't let me 
> > > > > > > select the cell in target spreadsheet.
> > > > > > >
> > > > > > > I'm loath to go back to Excel for this one spreadsheet but I've 
> > > > > > > been linking spreadsheets in Excel for so long and it works.
> > > > > > >
> > > > > > > I'd appreciate any Mac specific guidance.
> > > > > > >
> > > > > > > Bunk3m.
> > > > > > > PS on Digest mode.  Please cc me directly in addition to 
> > > > > > > answering to the list.


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted