Hi,
When I delete a table column with Cells.DeleteColumn, structured references to that column
in other formulas get silently re-pointed to the neighbouring column instead of becoming
#REF! the way Excel does. The formula keeps calculating but now returns the wrong column.
Short repro:
var wb = new Workbook();
var ws = wb.Worksheets[0];
ws.Cells["A1"].PutValue("Name"); ws.Cells["B1"].PutValue("Email"); ws.Cells["C1"].PutValue("Phone");
ws.Cells["A2"].PutValue("Bob"); ws.Cells["B2"].PutValue("bob@x.com"); ws.Cells["C2"].PutValue("222");
ws.ListObjects[ws.ListObjects.Add(0, 0, 1, 2, true)].DisplayName = "myTable";
var f = wb.Worksheets.Add("Report");
f.Cells["A1"].Formula = "=XLOOKUP(\"Bob\",myTable[Name],myTable[Email])";
Console.WriteLine(f.Cells["A1"].Formula); // =XLOOKUP("Bob",myTable[Name],myTable[Email])
ws.Cells.DeleteColumn(1); // delete the "Email" column
Console.WriteLine(f.Cells["A1"].Formula); // =XLOOKUP("Bob",myTable[Name],myTable[Phone]) <-- wrong
Expected: after deleting Email, the reference should become #REF!, not shift to myTable[Phone].
The same happens with current-row references ([@[Column]]): a Result column formula
=IF([@Key]="",[@Alpha],[@Alpha]-[@Beta]) becomes =IF([@Key]="",[@Beta],[@Beta]-[@Beta])
after deleting the Alpha column, and with InsertCutCells when moving a column.
Could you confirm whether this is a bug and whether a fix is planned? Thanks!