With VLOOKUP, you're searching for a value that should exist in the left-most column of a spreadsheet range, and returning a value a specified number of columns to the right of the first column in the range. You can only look for data in one direction (right), so whatever you're looking for has to exist to the right of your search key.
VLOOKUP(search key, range, index, FALSE)
search key: the value you're searching for
range: the range to consider for the search. The first column in the range is searched for the key specified in search key.
index: the column index of the value to be returned, where the first column in the range is numbered 1.
FALSE: there are very few cases where you'd use TRUE, so just use FALSE.
Specifically in those processes that include budget data or other numbers, SUMIF can be useful for providing your users with year-to-date expenditures against total budget allocations. It's really simple.
SUMIF(range,criterion,sum range)
range: the column which is tested against criterion
criterion: the value to search for within the range
sum range: the range where numbers corresponding to the rows in the range whose values matched the criterion are added together.
So, if I want to add together all cumulative expenditures for a particular school, I would sumif a range containing school names, on the criterion of a specific school name, returning the sum of the range containing purchase amounts.
For the most part today, we'll be using the QUERY function to create a list of requests that have not yet been approved or denied, in order to create our list of options in the approval form.
QUERY(data, query, -1)
data: the range of the data to be queried.
query: in quotation marks, SELECT the columns you want returned WHERE conditions are met.
IMPORTRANGE is the function that makes all of this possible. And, bonus, it's so easy to use.
IMPORTRANGE("spreadsheet key","range")
spreadsheet key: see that big bundle of letters and numbers dashes in the address bar of a Google Sheet? That's your spreadsheet key. Don't forget to put quotation marks around it.
range: in quotation marks, the sheet where your data resides, exclamation mark, the cell range for the data you want to return. Note that if the sheet name has spaces in it, you'll need to put single quotes around it. For example, "'Form Responses 1'!A:Z".
Note that the first time you link spreadsheets together in this way, you'll get a reference error until you give your new sheet access to your old sheet, which you can do by hovering over the cell containing the function.
Used in conjunction with formMule and autoCrat, this Add-On allows users to apply formulas to form response submissions as they are received; this allows for powerful, more personalized mail merges!
Add formulas to row in spreadsheet (we suggest row 2). Once you select copyDown from your Add-Ons menu, a sidebar will open with a simple "On / Off" switch. Switch to "On", select row containing formulas, and select all formulas you wish to copy down.
Flexible, easy to use document merge tool that creates PDF or shared Documents from spreadsheet data.
formMule uses Google form responses or spreadsheet data to send personalized emails to users. With options for up to 15 email templates based on various send conditions, formMule can help make tedious administrative tasks a lot easier. Optional form and time triggers allow you to automate routine communication!
formRanger provides the ability to dynamically populate form question choices from a column of data in a Google spreadsheet. You can populate multiple choice, list, checkbox, and grid style question options and set these options to refresh on form submit and/or hourly. Your forms will always contain the most up-to-date options possible!