Label referral request links by role

Say whether a link replaces, fulfills or shares a requisition with another request.

What you'll make

In Excel: replaces, SR-101 becomes Replacement of prior request: SR-101.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8Reads 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
4SWITCH( … )Picks the label that matches the role."Replacement of …: "
5&B8Adds 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: ")&B8

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. 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.
  3. 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