SharedFormula when referencing another sheet

Aspose.Cells Product Family
1

I am trying to implement the following formula using SharedFormula (Aspose cells):


cells["AB"].SetSharedFormula("=INDEX(Product!E2:E104,MATCH(AT5,Product!B2:B104,0))", 128, 1);


cells["AB"] refers to cell on first worksheet
Product is another worksheet in the same workbook.


Thanks
2
Hi,

Thanks for providing us details.

Well, for your case (as you are referencing to different sheets in the formula(s)) Shared Formula feature won't work. For your information, in MS Excel, if a formula refers to other worksheets, it will be copied to each cells and it is not applied/ added to shared formula(s) list. For confirmation, you may unzip the file (you may perform all the steps in MS Excel manually and save the file) and check the source .xml file for the sheet, you will see there is no shared formula with its attribute(s) described there. So, MS Excel actually copies the cell's formula from one cell to another cell but not set shared formulas.

For your situation, you should try to use Range.copy(sourceRange) accordingly, see the sample code segment for your reference and write your own code accordingly:
e.g
Sample code:

Workbook wb = new Workbook();
Worksheet worksheet = wb.Worksheets[0];
Cells cells = wb.Worksheets[0].Cells;
wb.Worksheets.Add("Product");
Cell cell2 = cells["AB1"];
cell2.Formula = "=INDEX(Product!E2:E104,MATCH(AT5,Product!B2:B104,0))";
Range range = cells.CreateRange("AB1");
Range range2 = cells.CreateRange("AB2:AB128");

range2.Copy(range);
wb.Save("e:\\test2\\out1.xlsx");

Thank you.