- What this sheet does
- A note on privacy before you start
- The template, column by column
- The formulas
- What to do when the flag fires
- Let AI help with entry and drafting, with a person checking
- What a person must still decide
Referrers don't usually send a note to say they've stopped referring to you. A GP, a support coordinator or a discharge planner just sends fewer people, then none. By the time anyone notices, months have passed.
The fix doesn't need new software. It needs a spreadsheet with the right columns and a few simple formulas.
Since 2017 I've worked inside an NDIS and aged care provider, and I built referrer lists there by hand in a sheet before bringing AI in to help. The sheet below is the same idea, tidied up.
What this sheet does
- Records every referral in one place
- Counts referrals per referrer per month
- Flags a regular referrer who has gone quiet
- Reminds you to say thank you
A note on privacy before you start
This sheet tracks referrers, not patients. Keep patient information to the minimum you need to match a referral to your own records. A client ID or file number from your practice software is usually enough. No names, no diagnoses, no clinical notes. Store the sheet somewhere with access limited to the people who need it, and follow your existing privacy policy.
The template, column by column
Make two tabs: Referrals (one row per referral) and Referrers (one row per referrer).
Tab 1: Referrals
| Column | What goes in it | Example |
|---|---|---|
| A. Date received | The date the referral arrived | 03/09/2026 |
| B. Referrer ID | A short code that matches the Referrers tab | R014 |
| C. Referral type | GP, support coordinator, LAC, allied health, discharge planner, self, other | Support coordinator |
| D. Service requested | Which of your services | Occupational therapy |
| E. Client ID | Your internal file number only. No name | C-2291 |
| F. Outcome | Booked, waitlisted, not suitable, no contact | Booked |
| G. Thank-you sent | Date, or blank | 05/09/2026 |
| H. Update sent | Date, or blank (see "what to do" below) | |
| I. Notes | Admin notes only, nothing clinical | Prefers email |
Tab 2: Referrers
| Column | What goes in it |
|---|---|
| A. Referrer ID | R014 |
| B. Name | The referrer's name |
| C. Organisation | Practice or organisation |
| D. Type | GP, support coordinator, etc. |
| E. Preferred contact | Phone, email, letter |
| F. Usual referrals per month | You set this, from history or your own judgement |
| G. Referrals in last 6 weeks | Formula (below) |
| H. Last referral date | Formula (below) |
| I. Status | Formula: "Quiet" or "OK" |
| J. Relationship owner | The staff member who looks after this referrer |
| K. Last contact and what was said | Short note |
The formulas
These work in Google Sheets and Excel. They assume your referral dates are in column A and referrer IDs in column B of the Referrals tab, starting at row 2.
Referrals in the last six weeks (in G2 of Referrers):
=COUNTIFS(Referrals!B:B, A2, Referrals!A:A, ">="&(TODAY()-42))
Last referral date (in H2):
=MAXIFS(Referrals!A:A, Referrals!B:B, A2)
Format H as a date. If a referrer has never referred, MAXIFS returns zero, which shows as an odd date around 1900 once formatted. You can ignore it. MAXIFS works in Google Sheets and in Excel 2019 or later (including Microsoft 365); older Excel versions don't have it.
The quiet flag (in I2):
=IF(AND(F2>=1, G2=0), "Quiet", "OK")
In plain words: "this referrer usually sends at least one a month, and has sent none in six weeks." Adjust the six weeks to suit your business. A referrer who sends one a quarter needs a longer window.
Then add conditional formatting so "Quiet" shows in a colour you'll notice, and sort by status once a week.
Monthly counts: for a view by month, insert a pivot table on the Referrals tab with Referrer ID as rows, Date received grouped by month as columns, and a count of Client ID as values.
What to do when the flag fires
A quiet flag is a prompt to check in, not to sell. Good options:
- A phone call from the relationship owner. Ask how things are and whether anything has changed on their side.
- A short update letter or email about your service: current wait times, a new clinician, a change in what you offer.
- Where it's appropriate and your privacy practices allow it, a progress update on a client they referred.
No gifts, vouchers or inducements to referrers. Rules on conflicts of interest and inducements can apply in health and disability services, so check your own obligations. The safe default is a call or a letter.
Let AI help with entry and drafting, with a person checking
Once the sheet works by hand for a month or two, AI can help in two places: pulling referral details from your inbox into a draft row, and drafting the check-in note. A person checks both.
I'm checking in with a referrer who has gone quiet. Draft a short, warm email or letter. This is a relationship check-in, not a sales message. No offers, gifts or incentives. No client details. Our business: [name, services, location] Referrer: [name, role, organisation] Our history: [e.g. referred regularly over the past year] Anything we know has changed: [e.g. new wait times, new clinician] What I'd like to ask: [e.g. whether anything has changed for them, whether our reports are useful] Sign off as: [name, role, phone] Under 120 words. Plain Australian English. Give me two versions.
Checklist
- Two tabs set up: Referrals and Referrers
- Client ID only, no names or clinical details
- Usual monthly count set for each regular referrer
- Formulas and conditional formatting working
- Each referrer has a named relationship owner
- Weekly check of the "Quiet" list booked in someone's calendar
- Thank-you column filled in
What a person must still decide
- What "usual" means for each referrer. The sheet only knows what you tell it.
- Whether a quiet flag matters. Some referrers are seasonal.
- Who calls, and what they say. The relationship belongs to a person.
- What client information is appropriate to share with a referrer, if any.
A referrer relationship is built by people. The sheet just makes sure nobody gets forgotten.
If you'd like this set up for you, that's what the free check is for.
General information only — not clinical, legal or financial advice. No client is named and nothing here describes a patient or participant. Written by Aishah Shah, Western Sydney.