Creating a Follow-Up Schedule Table Using Formulas in Google Sheets

Are you tired of manually keeping track of follow-up dates for your appointments or billing? Well, worry no more! With a few simple formulas, you can create a follow-up schedule table in Google Sheets that will help you stay organized and ensure timely follow-ups. Let’s dive in!

The Data You Need

To create a follow-up schedule table, you’ll need the following data:

1. The first follow-up date.
2. Person to contact.
3. Contact details (phone or email ID).
4. Number of times to follow-up.
5. Follow-up duration.

Now, let’s see how we can transform this information into a tabular form and create a follow-up schedule table in Google Sheets.

Sample Data

To illustrate the process, let’s consider an example. We have a person named Evan, and his first follow-up date is 13/06/2020. You want to contact him two more times, with a duration of 5 days between each follow-up.

Here’s what we want the follow-up schedule table to look like:

How to Create a Follow-Up Schedule in Google Sheets

Now, let’s get into the nitty-gritty of creating the follow-up schedule table using formulas in Google Sheets. I’ll guide you step by step, so don’t worry if you’re not familiar with formulas.

Step 1: Generate a Sequence of Duration Dates

Firstly, we need to generate a sequence of duration dates using the SEQUENCE function. To do this, follow the syntax below:

=iferror(SEQUENCE(1, No_of_follow-up_days, First_follow-up_date + How_often, How_often))

Replace the arguments in the formula with the corresponding references from your data. For example, in our case, the formula would be:

=iferror(SEQUENCE(1, D2, A2 + E2, E2))

Drag this formula down till you reach row 20 to generate the required sequence of duration dates.

Step 2: Combine Source Data and Duration Dates

Next, we’ll combine the source data columns in a specific way using the ampersand operators and flatten it using the FLATTEN undocumented function. Here’s the formula to achieve this:

=ArrayFormula(flatten('Follow-Up'!A2:A & "|" & 'Follow-Up'!B2:B & "|" & 'Follow-Up'!C2:C & "|" & 'Follow-Up'!F2:O5, "|"))

Replace the range references (‘Follow-Up’!A2:A, ‘Follow-Up’!B2:B, ‘Follow-Up’!C2:C, ‘Follow-Up’!F2:O5) with your actual data range.

Step 3: Follow-Up Schedule Table

Last but not least, we’ll edit the formula from step 2 and split the output using the pipe symbol as the delimiter in the SPLIT function to get the final follow-up schedule table. Here’s the modified formula:

=QUERY(ArrayFormula(SPLIT(flatten('Follow-Up'!A2:A & "|" & 'Follow-Up'!B2:B & "|" & 'Follow-Up'!C2:C & "|" & 'Follow-Up'!F2:O5, "|"))), "Select * where Col4 is not null")

This formula will give you the follow-up schedule table without any blank cells in the fourth column. If the dates in column A and D appear as date values, you can format them by selecting the columns and clicking on Format > Number > Date.

And there you have it! You’ve successfully created a follow-up schedule table in Google Sheets using formulas. Now you can easily keep track of your appointments or billing follow-ups.

Note: For more advanced tutorials on Google Sheets, you can visit Crawlan.com for additional resources.

That’s all for now. Happy scheduling!