Free Support Forum -

Worksheets.Name object is not properly handing some variations of RefersTo

Creating Named ranges in the Worksheets.Names collection.

If you set

xName.RefersTo = refersTo

refersTo should support both

    • =sheetname!C10
    • =sheetname!C[10]

In XMLXLS format, they should render as follows:

But, currently, if you assign

newName.refersTo = "=ToSpitfire!C10"

aspose.cells stores:
<ss:NamedRange ss:Name="zRawItems_DocItem_Description" ss:RefersTo="=<FONT>ToSpitfire!R[9]C[2]" />

The distinction is important because in the case of =FromSpitfire!C10, if you click on the column
the range name appears in the current range box. And if you cut and paste the range moves too! Which is critical for my application...

In the case of =ToSpitfire!C[10], cutting and pasting leaves the name "behing".

It would be terrific if you could correctly handle the version w/o the []!


Thanks for considering Aspose.

We will get back to you soon.

Thanks for being patient!


In XMLXLS formate, Excel use R1C1 Reference Style the formula. So if you want to assin R1C1 Reference Style formula , please try the following codes with the attached fix.

newName.refersTo = CellsHelper.ConvertR1C1FormulaToA1("=ToSpitfire!C10",0,0);

If you want to see the same formula as In XMLXLS file, please change the setting of Ms Excel:

"Tools"-"Option"->"Gerneral"->"Settings"->"R1C1 Reference Style",check this option.