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.

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.

Let our experienced consultants help you re-design your sheets and workflows to benefit from these valuable updates.  Contact us to arrange a free initial consultation call to discuss your needs. We’d love to hear from you!

The post #test Video Shortcodes appeared first on SmarterBusinessProcesses.

At Smarter Business Processes we offer you the following top tips to save you time and help improve workflow helping you with cross-sheet formulas and Smartsheet formulas.

Smartsheet formulas

Formulas and functions allow you to perform calculations and lookups within a single sheet or across multiple sheets. Smart sheet formulas also allow you to automate symbols and drop-down fields.

Both manually entered values and cell references can be used to build a smartsheet formula. You can add formulas to specified fields, or apply a uniform calculation to an entire column. You can also create formulas in the Sheet Summary to save space in your grid.

Formulas can include functions, such as SUM, AVG, and IF. There are also several unique functions exclusive to Smartsheet, including hierarchy functions like CHILDREN. Check out the Functions List to see all that are available.

Cross-sheet formulas

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.

Create cell links for significant time savings

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. The time saving is significant.

Save time with column formulas

Here’s how to apply column formulas to your Smartsheet.  The feature is designed to set the formula for the whole column including new rows. It removes the need to drag a formula down, saving you time and effort. 

Simply create the formula in one cell, then turn it into a column formula with a click of a button, and the entire column will fall into line. You have the assurance that your formula will always be present. It will remain locked in place, taking away the pain of editors changing it! 

This is a great way to protect formulae from accidental deletion or editing by collaborators, ensuring that every row receives that function.

Plus see how to use Column Formulas in sheets that use Row Hierarchy 

Powerful formulas

Additionally, when you add a new row, cells will be auto-filled with the appropriate smartsheet 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.

Top Tip for Smart sheet Formulas

1. Who has the capability to edit the formulas?

• Locked & unlocked fields: The sheet Owner and Admins can create and edit formulas in locked and unlocked fields.
• Unlocked fields only: The Owner, Admins, and Editors can create and edit formulas in unlocked fields.

2. Tips for easier sheet formula creation

• Use column formulas – When you need a formula applied consistently and uniformly to an entire column, column formulas are the perfect solution. Build your formula in a cell, and quickly convert it to apply to every cell in the column. Regardless of how new rows get inserted into the sheet, the column formula will automatically apply. Learn how to set formulas for all rows with column formulas.
• Use the sum tool – If you’re not sure which function to use, you can click the down arrow on the right of the Sum button in the toolbar and select a function. The Sum button will attempt to provide you with a formula based on the field you have selected in the sheet and any hierarchical relationships with the selected field. For example, selecting the field of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the field. After you create a formula, you can modify it at any time by double-clicking in the selected field or by pressing F2 (fn + F2 on a Mac).

What is a Cross-Sheet Formula?

Cross-sheet formulas are reference data in a different sheet to perform calculations or look up information in a specific cell.

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.

Let our experienced consultants help you re-design your sheets and workflows to benefit from these valuable updates.  Contact us to arrange a free initial consultation call to discuss your needs. We’d love to hear from you!

The post Make Smartsheet More Powerful with Cross-Sheet Formulas appeared first on SmarterBusinessProcesses.