Hands-on: build a placeholder watchlist
Instead of hard-coding placeholder strings into every formula, build a reference range and point your formulas at it. When the field rep finds a new placeholder, you update one cell and every formula picks it up.
Step 1 · Create a new sheet tab called placeholders
Right-click the bottom tab area and add a new sheet. Rename it placeholders.
Step 2 · List your placeholders one per row in column A
Starting in cell A1, type each placeholder on its own row:
A1: (000) - A2: N/A A3: TBD A4: unknown A5: none A6: ? A7: test
Step 3 · Reference it from Valid_Phone
Back in your main roster sheet, rewrite the Valid_Phone formula to use COUNTIF against the placeholders range:
=IF(OR(LEN(C2)<10, COUNTIF(placeholders!A:A, C2)>0), FALSE, TRUE)
Read it: “If the phone is too short OR the phone appears anywhere in the placeholders sheet, mark it FALSE. Otherwise mark it TRUE.”
Why this pattern is worth it
When the field rep tells you next week that staff have started writing pending instead of TBD, you do not refactor every formula. You add one row to the placeholders sheet. Every Valid_Phone, Valid_Email, and indicator formula updates instantly.
Action: Mark this page complete when you have finished the activity above.