Connecting Expense Report To Ledger

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 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”.