Connecting Expense Report To Ledger

Table of Contents

You can connect a template to a log spreadsheet. When users use the template, each submission will be linked to the log to allow easy organization and access. This tool is especially useful in the case of expense reports, given that it compiles reports submitted by many users. In the following guide, we will guide you through utilizing the Expense Report template and connecting it to a ledger spreadsheet.

Accessing Collavate’s Templates #

  1. Login to Collavate and go to the Template page.
  2. Then, click on the Public Template tab to access the many templates that Collavate provides for its users.
  3. On this page, search for the Expense Report template in the search bar and save it to your My Templates by clicking on the checkbox, clicking the Copy to button on the top right, and then finally, click My Templates in the dropdown menu.

Following the same process, copy the Database Ledger to My Templates. Return to the Public Templates page and search for Company Report Ledger and save it to your My Templates.

Connecting Expense Report template to a Database Ledger #

Now that the templates are in your Templates page, we will need to connect it to the ledger.

  1. Start by clicking on the Expense Report template you just saved in your My Templates.
  2. In the popup, click on the Edit tab then click on the Add result to tab in the following page.
  3. In this tab, click the Choose button and select the file named Database Ledger (you can use the search bar to find it easier).

Adding Process Information Variables #

After selecting the spreadsheet you want connected to the expense report template, you should see a drop down menu appear. These drop down menus contain process information variables which collect data based on each variable. You can choose to add more than one process information variable by clicking on the + icon.

In this case, we will need the following process variables:

  • Submission date (save in Column A)
  • Submitter’s name (save in Column B)
  • Final result of the process (save in column C)

Since we also want to keep track of the Total Expense, we will add another Process information variable. To do this, scroll down the list and click the + button. In the dropdown menu, select columns in process and in the following popup select the cell that contains the total estimated amount, in this case it is found in cell G23.

Click Run Test to send test data to the spreadsheet ledger to make sure the connection is working. You should see an alert saying the data was successfully copied. To verify the test data was successfully sent, click on the spreadsheet ledger and in that spreadsheet look for a second sheet titled CV-Process-Log. You should see the process information variables in the first row of that sheet.

Transferring Data from CV-Process-Log to the Styled Database Ledger #

To start, open the Database Ledger spreadsheet in Google Docs Spreadsheet. In our Database Ledger spreadsheet, there are four columns Date, Employee Name, Approval Status, and Total Amount.

We want the data from CV-Process-Log to show in our Database Ledger . To do this we will use the formula =INDIRECT(“‘CV-Process-Log’!G”&ROW(P2)) It is important to note that P2 is the first cell that will contain saved dates in CV-Process-Log. This formula will be added to cell B6 because Column B is where our saved dates will show in the Database Ledger. Next we want to click on the cell and click the little square in the bottom right corner and drag it all the way down to the bottom of the Database Ledger.

Now we will want to do the exact same for Employee Name, Approval Status, and Total Amount. Below are the formulas you will need. Remove the brackets when inserting the formula to the Database Ledger

  • Employee Name = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(G2))]
  • Approval Status = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(O2))]
  • Total Amount = [=INDIRECT(“‘CV-Process-Log’!G”&ROW(Q2))]

NOTE: Remember to drag the data explained above so that each cell in Database Ledger as a formula.

Now to see if our formulas are working, we will click Run Test in the Template edit page like we did above. After running the test, you should see test variables in the Database Ledger”.

Once you confirm you see the test variables, you are all set! Whenever you submit the template Expense Report the data of that process will be saved at CV-Process-Log sheet and inserted to the main spreadsheet of the Database Ledger.

Connecting Expense Report To Ledger

expense report

use case