How to Create an Order Tracker Sheet in Google Sheets

If your business involves making a product or doing a service that can span several business days, your customers will surely want to be updated on the progress of their order. 

For that, an order tracking system will be useful in updating them of the progress. 

You make a database where you update the progress of the order after every step. The customer, in turn, is given an access key where they can check the progress of their particular order  at any time.

We can construct a simple order tracker sheet in Google Sheets. For this we need to create two spreadsheets: one containing all the orders alongside their progress, and another spreadsheet  that can be directly accessed by the customer.

Create a spreadsheet containing the master list of progress

Creating a spreadsheet containing the master list of progress is relatively easy. The design ultimately depends on the specifics of your business, so we will use a relatively simple example.

Let’s say we operate a business that publishes books. Our simplified process has the following steps:

1. The manuscript is initially read by the editor (Initial reading)
2. The manuscript is checked for grammatical errors (Copywriting)
3. The manuscript is returned to author for checking (Return to author)
4. The manuscript is returned for typesetting (Typesetting)
5. The typeset manuscript is checked again for errors (Proofreading)
6. The design of the book is finalized (Final Design)
7. The book is printed (Printing)
   

We design the spreadsheet to have a set of columns containing the seven steps above, and also the following information:

1. Manuscript ID
2. Title of the Book
3. Author Name
   

So we have ten columns for our progress spreadsheet:

Refer to our previous article, How to Insert a Checkbox in Google Sheets, to learn how to add checkboxes in Google Sheets: 

We will reference this in our open spreadsheet where we can forward to our customers.

Create a spreadsheet that the customers can access

Next, we will create a spreadsheet that the customers can access, where they can input their tracking number and check the progress. Here are the steps:

Step 1: Design how the spreadsheet should look. In our example, I want it to look like this:

The customer will add the ID in the highlighted cell, and Google Sheets will fill up the succeeding cells in the row with information from the original spreadsheet. 

Step 2: Use VLOOKUP to reference the information from the original spreadsheet. Since the data is in another spreadsheet, you need to use IMPORTRANGE alongside VLOOKUP. You can use the following formula:

=vlookup(cell_containing_the_id,importrange("spreadsheet_url","source_range"),2,FALSE)

Where cell_containing_the_id is the cell where you want the ID added, spreadsheet_url is the url to the spreadsheet containing the master list of progress, and the source_range being the specific range within the spreadsheet, containing the master list of progress.

The third value of the VLOOKUP, the one with 2, corresponds to the column number in the source spreadsheet. We need to modify it to account for the other columns. As we follow the same columns as in the original spreadsheet, we simply number the succeeding cells with 3, 4, 5, and so on. 

Upon first adding the formulas in the cell, it will look as follows:

Simply click Allow access.

The #N/A error appears because the content of the cell below the ID label (A2) does not match any ID entry in the master spreadsheet. It will change once a matching ID is entered to the cell.

To check how VLOOKUP and IMPORTRANGE works, consult the following tutorials:

• How to Vlookup From Another Spreadsheet on Google Sheets
• How to Import Multiple Sheets Using the IMPORTRANGE Function
  

Step 3: Secure the cells from editing by protecting it. Select all the cells with the formula, then click the Tools option in the main menu. A drop-down menu will appear. Select Protect sheet.

A sidebar will appear on the right side of Google Sheets. Click Range, and the selected range is automatically added to the text box for the range. Click Set permissions.

A pop-up box will appear, where you can choose the access options. Select Restrict who can edit this range, then ensure the Only you option is selected. Click Done.

We are now done!

To check if it works, we can try it by adding an entry. Before:

After:

It clearly works!

You can further modify this by adding a range of values indicating if the order is under progress or is already finished for each step. For that you will use the SWITCH function. You can check our tutorial about it: SWITCH Function: An Alternative to IF Function in Google Sheets.

For a more sophisticated formatting style, you can use a set of drop-down boxes in the original spreadsheet. You can check the tutorial here: How to Add Yes/No Drop-Down Lists in Google Sheets

You can also use an elaborate IF function. You can check the tutorial here: Simple Guide To Using The IF() Function in Google Sheets