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

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