By combining a few formulas we can create a follow-up schedule table in Google Sheets. It will be useful for you to timely make billing or appointment follow-ups systematically.

As per my formula, to create a billing/appointment follow-up schedule in Google Sheets we require the following data.

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

Using these details in a tabular form, we can create a follow-up schedule table in Google Sheets. First, see the above details in a tabular form below.

Sample Data (Source Data to Create the Schedule):

We have the above basic details to create the follow-up schedule table.

As an example, let’s consider the person “Evan”. His first follow-up date (the date on which you have first contacted him) is on 13/06/2020 (cell A2).

You want to contact him again two more times (cell D2) for follow up that in 5 days duration (E2).

So in the follow-up schedule table, what we want is the below details (row # 2 and 3) correspond to him.

Schedule:

How to create the above table from the source data using formulas?

How to Create a Billing | Appointment Follow-up Schedule in Google Sheets

I am writing the formula for a maximum of 10 follow-ups for each contact person.

That means the maximum value in the fourth column (No. of follow-up days) in the ‘Sample Data’ table would be 10. I hope that would be enough. Let’s start.

In a Google sheets file enter the sample data. The entered data should be in the range A1:E5. I’ve named the tab as “Follow-Up”.

I have kept the total number of rows in this sheet to 20 that purely for performance enhancement. You may be able to use several rows (at least 500 rows) without any issues.

The total number of columns in this sheet (Follow-Up) is limited to 15. That means the last column is column O. In this the first 5 columns contain the sample data and the next 10 columns are for the 10 follow-ups.

Now follow the below steps starting from a drag and drop formula in cell F2. We are just preparing to create the follow-up schedule table using formulas in Google Sheets.

Generate a Sequence of Duration (How Often) Dates (Step 1)

First of all, see the Sequence function syntax (a function that generates sequential numbers or dates) and then a generic formula based on it.

Syntex: SEQUENCE(rows, [columns], [start], [step])

Generic Formula:

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

Arguments in the Function and Corresponding References:

rows – 1.
columns – No_of_follow-up_days.
start – First_follow-up_date + How_often.
step – How_often.

As per this generic formula, use the below Sequence formula in cell F2 and then drag-down until cell F20.

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

Now to the second step.

Combine Source Data and Duration Dates and Flatten (Step 2)

Now let’s start creating the follow-up schedule table using an array formula in a new tab titled “Sch” that in the same file.

In order to make you understand my formula clearly, in my formula, I am going to first use the range A2:O5 (closed range) instead of A2:O (open range) from the “Follow-Up” tab.

I am going to combine the source data columns in a specific way using the ampersand operators and then flattening it using the FLATTEN undocumented function. See that below.

Closed Range Formula

Here is the said formula which you may have seen in use in a few of my earlier tutorials especially for unpivoting tables in Google Sheets. Insert this formula in cell A1 in the “Sch” tab.

=ArrayFormula(
     flatten(
        'Follow-Up'!A2:A5&"|"&
        'Follow-Up'!B2:B5&"|"&
        'Follow-Up'!C2:C5&"|"&
        'Follow-Up'!F2:O5,"|"
     )
)

The above is an important part of creating the follow-up schedule table in Google Sheets. Before proceeding further to the final steps, let me explain how to use an open range in the just above formula.

Open Range Formula

Here are the array references to change the formula from ‘close’ to ‘open’ – ‘Follow-Up’!A2:A5, ‘Follow-Up’!B2:B5, ‘Follow-Up’!C2:C5 and ‘Follow-Up’!F2:O5.

Of course, we can use A2:A, B2:B, C2:C, and F2:O as open ranges. But I’m not following that here. Why?

Because, later, when creating the final follow-up schedule, I’ll use Split and Query functions to format the above combined and flattened data.

In those two functions, Query may not work correctly in such an open range. Because there would be a column (the first column) with mixed-type data (dates and pipe symbols) after the split. It would cause issues in Query.

So here is the proper method to use an open range for our above specific scenario.

I’m using Array_Constrain function to constrain the open range A2:A, B2:B, C2:C, and F2:O to the last non-blank cell in column A.

You can replace A2:A5 in the above formula with the below Array_Constrain.

array_constrain(
     'Follow-Up'!A2:A,
     MATCH(2,1/('Follow-Up'!A2:A<>""),1)
     ,1
)

For B2:B5 use the above Array_Constrain formula and change A2:A with B2:B.

array_constrain(
     'Follow-Up'!B2:B,
     MATCH(2,1/('Follow-Up'!A2:A<>""),1)
     ,1
)

Formula for C2:C5:

array_constrain(
     'Follow-Up'!C2:C,
     MATCH(2,1/('Follow-Up'!A2:A<>""),1)
     ,1
)

and finally the formula for F2:O5:

array_constrain(
     'Follow-Up'!F2:O,
     MATCH(2,1/('Follow-Up'!A2:A<>""),1)
     ,10
)

So the formula after the said changes will be as below.

=ArrayFormula(
     flatten(
        array_constrain(
           'Follow-Up'!A2:A,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
        )&"|"&
        array_constrain(
           'Follow-Up'!B2:B,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
        )&"|"&
        array_constrain(
           'Follow-Up'!C2:C,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
        )&"|"&
        array_constrain(
           'Follow-Up'!F2:O,MATCH(2,1/('Follow-Up'!A2:A<>""),1),10
        )
     )
)

Follow-Up Schedule Table (Step 3)

Here we are at the final stage of creating a follow-up schedule table in Google Sheets. We are going to edit the above formula in cell A1 in the “Sch” tab.

When you split the above formula output (wrap the above formula with Split) using the Pipe as the delimiter in the SPLIT function, you will get the said billing/appointment follow-up schedule table in an unformatted form.

=ArrayFormula(
     split(
        flatten(
           array_constrain(
              'Follow-Up'!A2:A,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
              'Follow-Up'!B2:B,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
              'Follow-Up'!C2:C,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
               'Follow-Up'!F2:O,MATCH(2,1/('Follow-Up'!A2:A<>""),1),10
           )
        ),"|"
     )
)

This formula may cause lots of blank cells in the 4th column. Using Query we can remove that. Here is the final formula in cell A1 in “Sch”.

=Query(
   ArrayFormula(
     split(
        flatten(
           array_constrain(
              'Follow-Up'!A2:A,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
              'Follow-Up'!B2:B,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
              'Follow-Up'!C2:C,MATCH(2,1/('Follow-Up'!A2:A<>""),1),1
           )&"|"&
           array_constrain(
               'Follow-Up'!F2:O,MATCH(2,1/('Follow-Up'!A2:A<>""),1),10
           )
        ),"|"
     )
   ),
"Select * where Col4 is not null"
)

This way we can create a billing/appointment follow-up schedule table in Google Sheets.

Note: The dates in column A and D may be returned as date values. In that case, select those columns and click on Format menu > Number > Date.

That’s all. Thanks for the stay. Enjoy!

Example_Sheet_13620

Resources:

• How to Create S Curve in Google Sheets and Its Purpose in Scheduling.
• Amortization Schedule in Google Sheets and Extra Principal Payments.