Is there an alternative to setSharedFormula, please? As per a previous discussion (VLOOKUP with setSharedFormula method breaks spreadsheet) I cannot use setSharedFormula with formulas that reference another worksheet, and I cannot use setFormula either because that just applies the same formula to each cell (i.e. it loses the relative references). Is there an alternative to setSharedFormula that will copy formulas down rows correctly like setSharedFormula does?
I do not think of any alternative to SharedFormula. As you need to copy the formula from the first row/cell to the below consecutive rows/cells, you may try to create a range based on your source cell(s), then copy that range to your destination cells. Not sure if this will help or work as it will again like setting shared formulas feature.
Anyways, we will further evaluate and check if there is some other better way to cope with it.
Like the operation in ms excel, you may set the formula to the first cell and then copy it to other cells in the range where you want to set formulas like shared formula. Aspose.Cells also provides a more convenient way to do that, code example:
cells["B1"].Formula = "=VLOOKUP(A1,Sheet2!A:B,2,FALSE)";
Range r = cells.CreateRange("B2", "B5");
r.CopyData(cells.CreateRange("B1", "B1"));