In open format Smartsheets, almost all cells are unlocked ('open'), providing users with the option to employ Excel formulas, number formatting (e.g., General, Currency, Date), text formatting (e.g., bold, italic), and cell color (background shading). Enabling these features in Smartsheets improves the product onboarding and maintenance process for suppliers by allowing more use of standard Excel functionality, as opposed to using non-open-format Smartsheets, which do not allow for formulas or visual enhancements like text formatting. Wrap Text functionality is also supported in open format.
The open format option is available for both data export (product maintenance) and template export (product onboarding) Smartsheets.
The following screenshot shows an open format Smartsheet that employs several features that are unavailable in non-open-format Smartsheets:
- Excel formulas. In this example, the formula uses a VLOOKUP function to pull data from an external spreadsheet.
- Currency and Accounting number formats.
- Various date formats.
- Text and cell formatting. This example uses bold text and a thick cell border.
Export Manager Configuration
To export an open format Smartsheet, choose 'Yes' for the Enable open format parameter on the 'Select Format' screen of the Export Manager wizard. The default value is No. For full details on how to configure Smartsheet export configurations, see the Smartsheet Data and Template Configurations topic in this guide here.
Default Cell Formats for Open Format Smartsheets
Since data export (product maintenance) sheets already contain data, and template export (product onboarding) sheets do not, the default cell format is different for each sheet, as follows:
- Open format data export (maintenance) Smartsheets are exported with all cells set to the Text format, which prevents the automatic conversion of exported number values that happens in the General format. For example, if a value with leading 0's (e.g., 0006) is exported with the General cell format, when the user opens the Excel file, Excel automatically removes the leading 0's, changing 0006 to 6.
- Open format template (onboarding) Smartsheets are exported with all cells set to the General format. The General format makes it easier for users to start entering formulas in the new sheet, as opposed to the Text format, which accepts formulas exactly as entered.
Regardless of the default cell formats, users can change formats on an as-needed basis in either type of open format sheet. For example, to enter a formula in Open Format data export sheets, change the cell format from Text to General.
Working with Formulas in Open Format Smartsheets
Open format Smartsheets allows formulas to be used in any cell where data can be entered. This includes functions such as VLOOKUP, which can populate values from another tab on the Smartsheet or from an external data source.
Though formulas are allowed on a limited basis in Smartsheets that use the 'working column' (more information on which can be found in the Smartsheet Data and Template Configurations topic in this guide here), formulas used in open format Smartsheets do not require that a 'paste special' operation be used to paste 'values only' into other cells. In open format Smartsheets, values derived from formulas can be left 'as-is.' STEP will recognize the cell contents as actual values and import the evaluations of the formulas and not the formulas themselves.
Note: When executing validation, product rows that contain formulas are always validated, even if the values have not changed. This might impact performance for large Smartsheets that use a significant number of formulas.
Validation of Formulas
When Smartsheets are validated, the following behavior is applied to formulas:
- The 'Validate Sheet' Smartsheet button sends the result of the formula evaluation for validation and not the formula itself.
- Cells that contain formulas are always validated, since their values can change along with the source data. Changing a value in an external cell in source data is treated as a change in the sheet.
Additional Considerations for Open Format Smartsheets
Because of the lack of restrictions in open format Smartsheets, additional considerations apply to their use, including:
- What you see in the sheet may not always be what you get on import, especially when using number and date formats.
- Unlocking cells for editing allows read-only cells to be edited (though any values in read-only cells are ignored upon import and validation).
- Background cell shading can be used, but in most cases, the shading is removed when the sheet is validated.
Conversely, some cells are still not editable (e.g., header rows and cells that fall outside of the range of 'active' Smartsheet columns and rows), and other standard Excel functionality is still unavailable, e.g., the ability to cut and paste entire columns and rows.
The following subsections address the most important considerations to keep in mind when working with open format Smartsheets.
Handling of Cells with Number Formats
Since cell number format options are available in open format Smartsheets, cells may contain values that are displayed in a particular locale. Numbers may also be displayed in various formats when currency, accounting, and percentage formats are applied.
Numeric data in cells that use either the General or Text format will be sent to STEP using the displayed value. For all other number formats, the underlying cell value is being used for validation and import, without any formatting. The validate and import implementations are otherwise identical to non-open-format Smartsheets.
Note: Number formatting is not supported in cells with multi-values.
The following table provides more specific details on how STEP validates and imports example text in open format Smartsheets:
|
Excel Number Format |
Excel Display Value Example |
Value Imported / Validated |
|
General |
$1234,586.80 |
Exactly as displayed in Excel. If the number is generated by a formula, then the imported / validated value is the result of the formula evaluation and not the formula |
|
Number |
89,324.34 |
The underlying number in the cell. E.g., the cell could be formatted to two decimal places, but the underlying number to import will actually be 89324.345 |
|
Currency |
£12,350.00 |
The underlying number in the cell without the formatting. E.g., 12350 |
|
Accounting |
$ 12,351.00 |
The underlying number in the cell without the formatting. E.g., 12351 |
|
Date |
18. oktober 2023 |
If the base validation type is Date then dates will be imported / validated as 'dd-MMM-yyyy' (E.g., 18-OCT-2023). If the base validation type is ISODateAndTime then dates will be imported / validated as 'yyyy-mm-dd HH:mm:ss' (E.g., 2023-10-18 08:17:40). If the base validation type is ISODate then dates will be imported / validated as 'yyyy-mm-dd' (E.g., 2023-10-18). Otherwise the underlying number without the formatting. E.g., 1234.5667 |
|
Time |
02:57 |
The underlying number in the cell without the formatting. E.g., 0.12291667 |
|
Percentage |
10020.00% |
The underlying number in the cell without the formatting. E.g., 100.2 |
|
Fraction |
1234 69/200 |
The underlying number in the cell without the formatting. E.g., 1234.345 |
|
Scientific |
1.23E+03 |
The underlying number in the cell without the formatting. E.g., 1234.5667 |
|
Text |
12345.6545 |
Exactly as displayed in Excel |
|
Special |
ISBN --1234-6 |
The underlying number in the cell without the formatting. E.g., 12345.66789 |
|
Custom |
£(12,345.14) |
If the cell contains a date or a time, then the value imported will be as for date / time cells. Otherwise, the underlying cell value will be imported / validated. E.g., -12345.1433 |
Read-Only Cells
In open format Smartsheets, since all cells are unlocked in the 'working area,' both the textual content and background shading of read-only cells can be edited, including special fields, like the ID field in data export / maintenance Smartsheets. This enables easier copying and pasting of values across cells.
The 'working area' of a Smartsheet is any cell where data is expected to be entered (attribute or reference columns).
Note: Though read-only cells are editable, any values in read-only cells are ignored upon import and validation of Smartsheets, even if the values have been changed. Additionally, if background shading is applied to a read-only cell, its color will return to pale yellow when the row is validated. This keeps the user informed of the cell's special meaning.
Background Shading and Text Formatting
Though cell shading and text coloring is available in open format Smartsheets, it is not recommended to rely on this formatting since it is almost always removed when Smartsheets are validated. Some considerations are as follows:
- All background shading and text coloration will be removed from an open format Smartsheet the first time it is validated. After the first validation, shading and text coloring can be re-added, but it will be removed on subsequent validations from cells in rows where data has been changed.
- Since cells that contain formulas are always validated, background shading will always be removed from cells and rows that contain formulas.
- The pale yellow background shading of read-only and conditionally read-only cells can be overwritten, which is not recommended since read-only cells can also be edited. If the shading is removed, this could be confusing for users who think that the changed content can be imported into STEP. When the sheet is validated, the pale yellow color is restored.
Note: Changed values in read-only cells will not be restored to their original value(s) after validation.
- The red shading of error-indicating cells can also be overwritten, which only affects the color, and not the error. When the sheet is validated, the red color is restored.
- Any text coloration, e.g., red, is removed upon validation. However, non-color-based formatting, such as bold, italic, and strikethrough, will remain after validation. Cell borders also remain after validation.
- Text styles (e.g., bold and italic) are for local use only, and are not imported into STEP. Text in STEP must still be formatted using features such as the Rich Text Editor in the Web UI.
Excel Conditional Formatting
Validation does not remove cell formatting that is applied using Excel's conditional formatting feature.
Due to this, it is advised to not use conditional formatting, since this formatting can override the red color of cells where validation has failed. I.e., if Excel conditional formatting is applied to a cell and that cell fails validation, the cell remains the conditional formatting color instead of red.
Copy and Paste Behavior for Rows and Columns
As with non-open-format Smartsheets, entire rows and columns cannot be cut and pasted / copied and pasted due to hidden, protected columns and rows within the Smartsheet that contain STEP-related information. The closest available functionality to 'copy and paste' row is the Duplicate Row functionality, which is available in template (onboarding) sheets. No duplication functionality is available for columns, since they are mapped to individual STEP attributes. For more information on using the Duplicate Row button, see the Using a Smartsheet topic in this guide here.