Show request and letter task status
A completed order doesn't mean its follow-up letter went out. Show both statuses.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | Takes the service request status. | "completed" |
| 2 | &" request; communication task="& | Adds the label for the task status. | "completed request; …" |
| 3 | IF(B8="","not linked",B8) | Adds the task status, or not linked if blank. | "… task=failed" |
Read it left to right. A blank task status shows as not linked.
=A8&" request; communication task="&IF(B8="","not linked",B8)How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own list. Paste each request's status and its linked communication task status into the green columns of the Template tab.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Check each task really belongs to this request first. Neither completed status proves the letter reached the person.
Microsoft's help: IF function
OTHER ROUTES YOU MIGHT LIKE

Label referral request links by role
replaces, SR-101 → Replacement of prior request: SR-101
Get the template →
Keep requester and performer apart
Ava Reed + ENT → Requester: Ava Reed; requested performer: ENT
Get the template →
Show a service ratio with both units
20 → 20 half-day visits per 1 month
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →