As work gets more complicated, and you’re capturing more data from different sources, working across multiple sheets becomes the norm. Yet connecting data across sheets can be a challenge, especially when dealing with large sets of data. Today, we’re excited to announce that we’ve added cross-sheet formulas to Smartsheet — including VLOOKUP, one of our top customer requests.
Cross-sheet formulas fundamentally change the way you can manipulate data in Smartsheet, giving you more flexibility and power to create connections across all of your team’s work and information, regardless of what sheet that data is in.
Instead of repeatedly entering the same data into multiple sheets, or manually searching through large sheets to copy and paste or create cell links, you can enter a formula once at the top of a column and copy it into the column’s other cells.
Additionally, when you add a new row, cells will be autofilled with the appropriate formulas. This is particularly powerful on a sheet being populated by a form, as the lookup formula information will be added to new submissions without the need to constantly monitor the sheet.
What is a Cross-Sheet Formula?
A cross-sheet formula references data in a different sheet to perform calculations or look up information in a specific cell.
Here’s how to reference another sheet in Smartsheet:
Lookup formulas commonly include VLOOKUP, INDEX, and MATCH. VLOOKUP is used to retrieve corresponding data from other sheets. For example, when using VLOOKUP you could use a customer ID to look up the primary account contact’s name and pull that info into a sheet. When used together, INDEX and MATCH offer an additional, more flexible way to look up information across sheets.
Moving forward VLOOKUP is replacing LOOKUP in Smartsheet, but all of your existing formulas created using LOOKUP will continue to work.
The Power of Connected Data
Now that you can connect data across multiple sheets, you can organize that data much more efficiently. With VLOOKUP and INDEX/MATCH in your toolkit, you’ll save time by using sheets as lookup tables to pull relevant information into another sheet as needed, and you’ll also cut down on the mistakes that can arise from copying and pasting or importing and exporting data.
Here’s an example of how an IT manager uses cross-sheet formulas VLOOKUP and COUNTIF to assign and track IT tickets.
You can also use cross-sheet formulas to pull together data for your reports and Sights™. For example, a Sales VP might want to count quarterly sales opportunities, add up expected revenue from each region, and pull that into a report or Sight. Using SUMIF and COUNTIF in cross-sheet formulas, they can quickly create a roll-up from one or more sheets to serve as the source sheet.
Accelerate Execution With Cross-Sheet Formulas
Having the right information at the right time is critical for you to improve and accelerate decision-making in your organization, and act on those decisions more quickly.
In addition to supporting better decision-making, cross-sheet formulas also support alerts and actions in Smartsheet. Once they’re set up, cross-sheet formulas will update data in the destination sheet to reflect real-time changes in the underlying source data. These updates can trigger notifications, update requests, and approval requests set up in the destination sheet.
Cross-sheet formulas make it quick and straightforward to connect your data so you can focus on the information you need to move work forward, and automate steps based on data stored in multiple sheets to help you execute more quickly. To learn more about how to use cross-sheet formulas,read our help article here.
Join us on February 15th at10 am PSTor3 pm PSTfor our next Work Smart Webinar: How to Improve Performance with Formulas Across Sheets. This free 30-minute webinar will show you how cross-sheet formulas help make data connections across all of your team’s work faster and easier.