How It Works
We’ve created a ‘lite’ version of a spreadsheet that can be used to capture workplans, complicated budgets, checklists, and more. You can add calculated columns and open up select columns for editing at any time, which means applicants can amend their budgets or provide reporting data alongside the data that they originally provided when they submitted the application. Each program can have up to five tables in the Tables tab.
How To Set It Up
|1.||Click into the Settings tab.|
|2.||Turn the Tables module ‘on’.|
|3.||Click into the Tables tab.|
|4.||Give the tab a label.|
|5.||Set up your tables. Each of the five tables is set up in the same way, so we’ll just set up the first one.
*See below for more detailed instructions
|5.||Use the Applicant View to check your work.|
In each table, there are 20 columns to use to capture applicant data.
Line Items Column
The first column is, by default, the Line Items Column. This is the column into which you will put your line items, much like the very first column in a normal spreadsheet.
Be sure to put something into the Line Items Column Header field to ensure that your table displays correctly. If you do not have an official header to use for that column, simply enter a space.
For the rest of the columns, you add the text that will act as the header on that column, indicating to applicants what you’d like them to enter in that column. For example, if you’re using the table to capture budget information, you may want to set up Column A as “Last Year’s Budget”, Column B as “This Year’s Budget”, and maybe use Column C as a place for the applicants to enter a description or narrative explanation for this year’s budget numbers. To do that, you’d input that text into the proper fields in the column setup section.
For every column, there are three other settings to select:
- Label – $, people, cookies, etc. To create a left-justified label (such as foreign currency), enter the desired label then a dollar sign in parentheses, like this:($). (Single dollar signs – $ – are automatically left-justified.)
- Answer Size
- Small – 10 characters; Intended for related numerical data; Automatically totals at the bottom of the column
- Medium – 25 characters
- Large – 65,000 characters; Applicants can resize the text box by clicking and dragging in the lower right corner
- Editable – **This is an important setting, because it determines whether or not your applicants can input data into the column.** If you do NOT check this box, your applicants will not be able to fill in that column. This includes the Line Items Column – even if one of your line items is editable, it will not actually be editable unless the Editable box is checked for the Line Items Column.
You can add an unlimited number of line items/rows in each table. Type in your line items, one on each line. There are also commands that you can add to the end of a line item to tell the system to do something special with that item or row or to insert a space or blank, editable row:
- HEADER – to create a bold header using the preceding word or phrase
- SUBTOTAL – to put a subtotal after that line item (resets to zero, Grand Total will automatically be calculated)
- SECTIONTOTAL – to put a section subtotal of all previous subtotals (resets to zero)
- SPACE – to put a space without fillable boxes between the preceding and following line items
- EDIT – to allow the line item title to be edited or to add a blank, editable line (NOTE: Remember that the line item will only be editable if Editable checkbox is also checked for the Line Items Column.)
- EXCLUDE – to prevent numeric data from being used in totals and calculations
A couple of notes on the line items:
First, be sure to finish your list with a final carriage return. Type your last line item, hit the ‘Return’ or ‘Enter’ key on your keyboard one more time to put your cursor on the next line, then click outside the box to save your change. The last empty line signals to the system that that is the end of the line items to be presented. If you do not end your list on a carriage return, the system will not display your final line item.
Second, if you’re allowing your applicants to input their own line items and you aren’t sure how many blank fields your applicants will need, err on the side of adding a few extra or using SPACE as a placeholder to allow you to add more lines later. Once the program has opened, you cannot add more lines in between existing lines without creating issues in the data received from applicants.
Up to five Calculated Columns can be set up on each table. Unlike the Budget Calculated Columns, these calculations are visible to the applicants in their applications, but they do not calculate automatically. We recommend adding a note to that effect in the instructions for the tab or in each table. Here’s some suggested text for that note:
**TOTALS ARE CALCULATED ON PAGE REFRESH** To see the calculated totals, input your numbers then use the grey Refresh Page button (above) to reload the page with the fresh totals.
Enter a title for the calculated column in the Column Title box, then enter a label for the column. To create a left-justified label (such as foreign currency), enter the desired label then a dollar sign in parentheses, like this: ($). (Single dollar signs – $ – are automatically left-justified.) Next, type a formula in the Formula box. Use the letters next to the table column titles in the column setup to denote the columns to use in the formula. Only “small” columns can be used in calculations. Acceptable operators are:
- + (addition)
- – (subtraction)
- * (multiplication)
- / (division)
Parentheses can be used to create more complex formulas. An equals sign (=) is not necessary. To position the calculated column after a specific column in the table, enter that column letter in the Positioned After box.
This is one of our more complicated features, so feel free to contact us if you have questions, need help, or want some tips on how to input your tables!