Skip to content

Instantly share code, notes, and snippets.

@ExcelRobot
Last active March 28, 2022 17:21
Show Gist options
  • Save ExcelRobot/34340d36b0005d9ac2993a4a6a1a5cc8 to your computer and use it in GitHub Desktop.
Save ExcelRobot/34340d36b0005d9ac2993a4a6a1a5cc8 to your computer and use it in GitHub Desktop.
Is Formula Overridden LAMBDA Function
/*
Name: Is Formula Overridden (IsFormulaOverridden)
Description: Checks whether the cell is either not a formula or
a formula that contains no letters (ie: =123.45+678.90). Can
be used with conditional formatting to highlight when users
hardcode numbers over your formulas.
Author: Excel Robot (@ExcelRobot)
Category: Auditing
*/
IsFormulaOverridden=LAMBDA(Cell,
LET(
IsFormula, ISFORMULA(Cell),
Formula, FORMULATEXT(Cell),
Characters, UPPER(MID(Formula, SEQUENCE(1, LEN(Formula), 1), 1)),
LettersOnly, IF((CODE(Characters) >= CODE("A")) * (CODE(Characters) <= CODE("Z")), Characters, ""),
HasNoText, LEN(TEXTJOIN("", TRUE, LettersOnly)) = 0,
IsOverridden, IF(IsFormula, HasNoText, TRUE),
IsOverridden
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment