Ignore ErrorsIn Cell Range

Sometimes, when working with numbers and calculating formulas, Excel evaluates for any errors and points out the errors by showing the green triangle at the top-left corner of the cell. To avoid error evaluation and showing errors with the green triangle, GcExcel provides IgnoredError property in IRange interface and IgnoredErrorType enumeration to enable you to ignore errors such as invalid formula results, numbers stored as text, inconsistent formulas in adjacent cells, and others, and not show the green triangle at the top-left corner of the cell in a specific cell range in Excel.

IgnoredError property will not change when copying or cutting rows, columns, or cells, whereas it will move or delete when inserting or deleting rows, columns, or cells. The property will be copied or moved when copying or moving the sheet. IgnoredError property of the top-left cell of the first cell rect will be returned when getting IgnoredError.

GcExcel supports ignoring the following types of errors:

Error Type	Description
None	No errors are ignored.
InconsistentListFormula	Ignores the error of discrepancies in formulas within a calculated column.
InconsistentFormula	Ignores the error of discrepancies in formulas within a range.
OmittedCells	Ignores the error in cells containing formulas referring to a range that omits adjacent cells that could be included.
TextDate	Ignores the error when formulas contain text-formatted cells with years misinterpreted as the wrong century.
EmptyCellReferences	Ignores the error when a formula contains a reference to an empty cell.
ListDataValidation	Ignores the error of the cell value that does not comply with the Data Validation rule that restricts data to predefined items in a list.
EvaluateToError	Ignores the error of the formula result.
NumberAsText	Ignores the error in cells containing numbers stored as text or preceded by an apostrophe.
UnlockedFormulaCells	Ignores the error in unlocked cells containing formulas.
All	Ignores all types of errors.

Refer to the following example code to ignore all types of errors in the specified range:

// Create a new workbook.
var workbook = new Workbook();

// Add data object.
object[,] data = new object[,]{
    {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
    {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", "67", "165"},
    {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", "76", "176"},
    {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", "68", "145"}
};

// No errors are ignored in this range.
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A:F"].ColumnWidth = 15;
worksheet.Range["A1:F1"].Merge();
worksheet.Range["A1:F1"].Value = "Ignores No Range Errors";
worksheet.Range["A1:F1"].Font.Bold = true;
worksheet.Range["A1:F1"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A2:F5"].Value = data;
worksheet.Tables.Add(worksheet.Range["A2:F5"], true);

// Ignores all errors in this range.
worksheet.Range["A7:F7"].Merge();
worksheet.Range["A7:F7"].Value = "Ignores All Range Errors";
worksheet.Range["A7:F7"].Font.Bold = true;
worksheet.Range["A7:F7"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A8:F11"].Value = data;
worksheet.Tables.Add(worksheet.Range["A8:F11"], true);

// Ignore error in range A8:F11.
worksheet.Range["A8:F11"].IgnoredError = IgnoredErrorType.All;

// Save Excel file.
workbook.Save("IgnoreRangeError.xlsx");