If the defined name is correctly set, formula calculation result will be corrected. So it is a formula issue and also a defined name issue.
2012/8/13 shzh zhao <aoo.zhaos...@gmail.com> > I think it is a formula issue and not belong to name scope range. > > 2012/8/10 Jianyuan Li <lijianyuan1...@gmail.com> > > > Hi, Wang Lei, > > > > Below is my clarification for issue > > https://issues.apache.org/ooo/show_bug.cgi?id=119565. Comment is also > > added > > in Bugzilla. > > > > In Excel 2003 define a defined name as > > "=Sheet1!$D$3:$D$5,Sheet1!$B$3:$B$5". In this defined name, a union > > operator(Excel uses "," and AOO uses "~") is used to form a union area. > > This defined name will be passed into a formula SUM. Import them in AOO, > by > > Excel 2003 filter the union operator will be translated into ";" which is > > an old union operator. And SUM will take it as a parameter separator. So > > the SUM result seems correct while it is not in AOO. > > Union operator should be set as "~" in defined name in AOO. But a fix in > > Excel 2003 filter is not reasonable because: > > 1. Defined name with union(OO uses ";") created in lower version > > OO(OOo2.4.3) still cannot be imported correctly even if a fix is patched > in > > Excel 2003 filter. > > 2. I have checked this formula "=SUM((B3:B5,D3:D5))"(union directly used) > > created in Excel 2003. It will be imported as "=SUM((B3:B5~D3:D5))" which > > is correct. While Excel 2003 filter still imports the union as ";". But > as > > last it is "~". Core function must does something which a hint to fix > union > > issue in defined name. > > > > So please take consideration for it in the enhancement. > > > > > > Regards, > > Jianyuan > > > > 2012/8/8 Lei Wang <lei.wang.l...@gmail.com> > > > > > Hi Regina > > > > > > I saw your comments in > > > https://issues.apache.org/ooo/show_bug.cgi?id=120478 > > > > > > It is glad to know that ODF1.2 support sheet scoped defined name. It is > > > very important to comply with ODF1.2. So our plan will be changed to > > > Step 1, support interoperability with sheet scoped defined name in > Excel. > > > Because the solution is ready. it is our first priority. > > > Step 2, support loading and saving ods file for sheet scoped defined > > name. > > > We will put it in our AOO3.5 plan. > > > Step 3, support create sheet scoped defined name in GUI in AOO. > > > > > > On Wed, Aug 8, 2012 at 5:00 PM, Regina Henschel < > rb.hensc...@t-online.de > > > >wrote: > > > > > > > Hi shzh zhao, > > > > > > > > there is no need for modifying ODF, named-expressions with scope on > one > > > > sheet are already specified and LibreOffice reads and writes such > > > documents. > > > > > > > > I think the order should be the other way round. First make AOO read > > and > > > > write such named-expressions in ODF and then improve the import and > > > export > > > > filter. > > > > > > > > Kind regards > > > > Regina > > > > > > > > shzh zhao schrieb: > > > > > > > > hi, > > > >> > > > >> here is an interoperability issue in Aoo. > > > >> When loading VBA that contains defined name in Aoo, the names are > > > changed > > > >> to a new one if the name is limited > > > >> in a worksheet in MS office.This will cause big problem. > > > >> > > > >> After my investigation,I found it is an limitation of Aoo. Because > MS > > > >> Excel > > > >> 2003 can support worksheet scope defined name. but it can't be set > in > > > UI. > > > >> and Excel 2007 can define duplicate name for different sheet in its > > name > > > >> manager dialog. > > > >> When AOO loading a xlsx/xls file which has duplicated defined name, > > AOO > > > >> will only change the name to a unique one. But any other place which > > > uses > > > >> the defined name is not changed, this is the root cause. > > > >> > > > >> this enchancement will be completed in 2 steps. > > > >> in Currently step, just focus on the interoperability of MS defined > > > >> name.,and not modify the ODF file format. > > > >> > > > >> if MS office defined range names with a sheet or a work book,import > it > > > in > > > >> Aoo, > > > >> make the name of work book in the original,but with a flag of global > > > >> name,for example,the name is "Name_1",it will be "Name_1" as before > in > > > >> the define name dialog. > > > >> make the name of work sheet to the original,but with a flag of sheet > > > >> name,for > > > >> example,the name is "Name_1",it will be "Name_1 (sheet 1)" in the > > define > > > >> name > > > >> dialog if the name is defined in the sheet 1. > > > >> > > > >> here is the issue link: > > > >> https://issues.apache.org/ooo/**show_bug.cgi?id=120478< > > > https://issues.apache.org/ooo/show_bug.cgi?id=120478> > > > >> > > > >> > > > > > > > > > > > > > -- > * > > > mailto: *aoo.zhaos...@gmail.com <https://google.com/profiles> > <https://google.com/profiles> >
