Label referral request links by role
Say whether a link replaces, fulfills or shares a requisition with another request.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | Reads the link role for this row. | "replaces" |
| 2 | "replaces", … | replaces: labels it as a replacement of a prior request. | "Replacement of …: " |
| 3 | "basedOn", … "requisition", … | basedOn or requisition: labels a fulfilled or shared request. | Not used for row 8 |
| 4 | SWITCH( … ) | Picks the label that matches the role. | "Replacement of …: " |
| 5 | &B8 | Adds the reference after the label. | "Replacement … SR-101" |
Read it left to right. Any other role gets the label Review request link role.
=SWITCH(A8,"replaces","Replacement of prior request: ","basedOn","Fulfills higher-level request: ","requisition","Shared requisition identifier: ","Review request link role: ")&B8How 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 links. Paste each link's role (replaces, basedOn or requisition) and its reference into the green columns of the Template tab. The labels fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Take each role from the source field itself. A shared requisition doesn't mean one request replaces another.
Microsoft's help: SWITCH function
OTHER ROUTES YOU MIGHT LIKE

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 →
Show request and letter task status
completed + failed → completed request; communication task=failed
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →