Cell formula doesn't get set with formula without External Ref

Aspose.Cells Product Family
1

Hi team,

I have a formula which is referring to Custom Function but in a different workbook. I’m trying to remove the reference part by replacing the formula (without ref), but it remains the same!

In attached workbook in cell (1,1) there is this formula :
=IF(AR20=“”,“”,‘C:\Users\YasharHeydari\Downloads\Netflix NFLX US Merge Example (1).xlsx’!_xldudf_AS(AR20,“name”))

when I replace it with :
=IF(AR21=“”,“”,_xldudf_AS(AR20,“name”))

using the following code, it still remains the same with reference!

Cell cell = workbook.Worksheets[“Summary”].Cells[0,0];
cell.Formula = “= IF(AR21 = "", "", _xldudf_AS(AR20, "name"))”;
save()

Could you please check why this is happening?
Thank you
NFLX_FormulaTest.zip (8.5 KB)

2

@yashar.heydari

Cause

The issue you’re experiencing is likely due to the way the formula is being set in the cell. When you replace the formula, it may not be updating correctly if the workbook is not being saved properly or if there are references that are still being retained.

Solution

To ensure that the formula is updated correctly, you can try the following steps:

  1. Make sure to save the workbook after setting the new formula.
  2. Check if there are any settings or properties that might be preventing the formula from being updated.
  3. Use the CalculateFormula method to force the workbook to recalculate after changing the formula.

Code

Here’s a code snippet that demonstrates how to replace the formula and save the workbook:

// Load the workbook
Workbook workbook = new Workbook("path_to_your_workbook.xlsx");

// Access the specific cell
Cell cell = workbook.Worksheets["Summary"].Cells[0, 0];

// Set the new formula
cell.Formula = "=IF(AR21=\"\", \"\", _xldudf_AS(AR20, \"name\"))";

// Force recalculation of formulas
workbook.CalculateFormula();

// Save the workbook
workbook.Save("path_to_your_updated_workbook.xlsx");

Next Steps

  1. Run the updated code to see if the formula is replaced correctly.
  2. If the issue persists, check if there are any external references or settings in the workbook that might be affecting the formula update.
  3. If necessary, provide a minimal reproducible example to further diagnose the issue.

Please proceed with these steps, and let me know if you need further assistance!

3

I tried above suggestions including CalculateFormulas and it’s still the same! I already provided a sample code and workbook above. Also it doesn’t matter what the custom function is, e.g. the following is the same :
Original formula:
=IF(AR20=“”,“”,‘C:\Users\YasharHeydari\Downloads\Netflix NFLX US Merge Example (1).xlsx’!ASQ(AR20,“name”))

Replacement formula :
“= IF(AR21 = "", "", ASQ(AR20, "name"))”;

formula doesn’t change in the result.

4

@yashar.heydari,

Thanks for the sample Excel file and details.

Since your Excel file contains external links to another workbook. To address this, first remove the external links and then update the formula as needed. The following approach ensures that the external links are eliminated, and the formulas are adjusted appropriately in the output Excel file…
e.g.,
Sample code:

 // Load the workbook
Workbook workbook = new Workbook("e:\\test2\\NFLX_FormulaTest.xlsx");
/*
if (workbook.Worksheets.ExternalLinks.Count>0) //Process external links if it has
{
    var links = workbook.Worksheets.ExternalLinks;
    Console.WriteLine(links.Count);//1 
    for (int i = 0; i < links.Count; i++)
    {
        Console.WriteLine(links[i].DataSource);
        Console.WriteLine(links[i].IsReferred);
    }
}
*/
// Remove the first external link
workbook.Worksheets.ExternalLinks.RemoveAt(0);
// Access the specific cell
Aspose.Cells.Cell cell = workbook.Worksheets["Summary"].Cells[0, 0];

// Set the new formula
cell.Formula = "=IF(AR21=\"\",\"\",_xldudf_AS(AR20,\"name\"))";

// Save the workbook
workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

5

@yashar.heydari

Instead of removing the external link, you may specify FormulaParseOptions.CheckAddIn as false when setting formula. Please change the code:

to

cell.SetFormula("= IF(AR21 = \"\", \"\", _xldudf_AS(AR20, \"name\"))", new FormulaParseOptions() { CheckAddIn = false });
6

@amjad.sahi and @johnson.shi

Thank you both for the suggestions. I’m not sure about the possible side-effects of removing External Links as there may be different external links in the sheets and detecting the one I should remove is a little bit risky. But I think using formula parse option in this case is less risky, so I’ll try that one.

Thank you

7

@yashar.heydari,

You are right to be cautious here. When working with external links in Excel files can have unintended side effects if removed but not handled precisely, especially when formulas refer to external workbooks. So, you may use SetFormula() with FormulaParseOptions.CheckAddIn = false to carefully update specific formulas (only) referencing external workbooks.