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:
The manuscript is initially read by the editor (Initial reading)
The manuscript is checked for grammatical errors (Copywriting)
The manuscript is returned to author for checking (Return to author)
The manuscript is returned for typesetting (Typesetting)
The typeset manuscript is checked again for errors (Proofreading)
The design of the book is finalized (Final Design)
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:
Title of the Book
So we have ten columns for our progress spreadsheet:
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:
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: