Formulas and Functions https://help.smartsheet.com/ en Create and Edit Formulas in Smartsheet https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet <span class="field field--name-title field--type-string field--label-hidden">Create and Edit Formulas in Smartsheet</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>Use formulas to calculate numeric values or automate aspects of your sheet. You can create formulas, for example, to sum a range of expenses, or to check a box when a specific value is entered in a field.</p> <hr /> <h3><a id="create"></a>Create a formula</h3> <p>You can create a formula in the following field types:</p> <ul> <li>Fields <em>not</em> being used for dependencies or resource management. For more information, see <a href="/articles/765727">Enable Dependencies and Use Predecessors</a>.</li> <li>Text/Number (as long as they're not being used in dependencies or resource management)</li> <li>Contact List fields (as long as they're not being used in resource management)</li> <li>Date (as long as they're not being used in dependencies or resource management)</li> <li>Dropdown List</li> <li>Checkbox</li> <li>Symbol</li> <li>% Allocation</li> </ul> <p>To place a formula in a field:</p> <ol> <li>Select the desired field.</li> <li>Type an equal sign in the field, and then type the calculation.<br /> <img alt="5+3" data-entity-type="file" data-entity-uuid="a1f198a2-53e5-4e61-8d45-caf97f80b033" src="/sites/default/files/inline-images/5_3-197434.PNG" width="179" height="64" loading="lazy" /></li> <li>After you’ve typed in your formula, press [Enter].<br /> <img alt="8" data-entity-type="file" data-entity-uuid="da01d80c-9e5b-44bb-a1e6-d8e82164fd8f" src="/sites/default/files/inline-images/Press_Enter-197432.PNG" width="170" height="71" loading="lazy" /><br /> The result is displayed in the field containing the formula.</li> </ol> <blockquote> <p>Formulas can be placed in Contact List fields. Automatically assign people to tasks with a VLOOKUP formula, for example. Note that formulas can’t be placed in Contact List fields being used for resource management.</p> </blockquote> <h4><a id="operators"></a>Functions</h4> <p>Formulas can include functions, such as SUM, AVG, or IF. Functions perform calculations with or manipulate data in sheets. For the complete list of functions available in Smartsheet, see the <a href="/functions">Smartsheet Functions List.</a></p> <h4>Formula operators</h4> <p>You can use the following operators in Smartsheet formulas.</p> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <tbody> <tr> <td><strong>Symbol</strong></td> <td><strong>Description</strong></td> </tr> <tr> <td>+</td> <td>Add</td> </tr> <tr> <td>-</td> <td>Subtract</td> </tr> <tr> <td>*</td> <td>Multiply</td> </tr> <tr> <td>/</td> <td>Divide</td> </tr> <tr> <td>^</td> <td>Exponent</td> </tr> <tr> <td>&lt;</td> <td>Less than</td> </tr> <tr> <td>&gt;</td> <td>Greater than</td> </tr> <tr> <td>&gt;=</td> <td>Greater than or equal to</td> </tr> <tr> <td>&lt;=</td> <td>Less than or equal to</td> </tr> <tr> <td>=</td> <td>Equal to</td> </tr> <tr> <td>&lt;&gt;</td> <td>Not equal to</td> </tr> </tbody> </table> <h4> </h4> <hr /> <h3><a name="edit" id="edit"></a>Edit an existing formula</h3> <p>To edit an existing formula:</p> <ol> <li><strong>Double-click</strong> a field containing the formula to open it for editing.</li> <li>Make your desired formula changes, and then press Enter.<br /> <br /> If you change your mind about editing the formula, press Esc to exit edit mode and revert to the pre-edited formula.</li> </ol> <hr /> <h3><a id="tips"></a>Tips for easier formula creation</h3> <h4>Use column formulas</h4> <p>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. For more information, see <a href="/articles/2481944">Set Formulas for All Rows with Column Formulas</a>.</p> <h4>Use the sum tool</h4> <p>If you're not sure which function to use, you can click the down arrow on the right of the <strong>Sum</strong> button in the toolbar and select a function.<br /> <br /> The <strong>Sum</strong> 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 <strong>Sum</strong> icon will produce a =SUM(CHILDREN()) formula in the field. </p> <p>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).</p> <h4><a name="percent" id="percent"></a>Work with percentages</h4> <p>Smartsheet treats percentages as values between 0 and 1. When you create formulas in columns formatted for percent (using the <img alt="percent-button" data-entity-type="file" data-entity-uuid="b37d8850-a614-4833-9bd8-de1414f5f261" src="/sites/default/files/inline-images/percent.png" width="40" height="44" loading="lazy" /><strong> </strong><strong>Percentage Format</strong> button in the toolbar), use <strong>decimal values</strong>. For example...</p> <p>=0.5 + 0.4</p> <p>...will return <strong>90%</strong> in a column formatted for percentage...</p> <p>=5 + 4</p> <p>...will return <strong>900%</strong> in a column formatted for percentage.</p> <h4>Copy a formula with drag-fill or auto-fill </h4> <p>If you have a formula that you’d like to use in multiple fields without having to manually type the formula in each field, use the following methods to quickly copy the formula to other areas of your sheet:</p> <ul> <li>Drag-fill—You can drag from the lower-right corner of a selected field to copy a formula across contiguous fields in the sheet. As you copy, the formula will automatically change its respective field references.</li> <li>Auto-fill—You can have Smartsheet automatically copy a formula to new, vertically adjacent fields that enter the sheet. When auto-filled, the new formula will automatically change its respective field references.</li> </ul> <p>Learn more about this functionality in the help articles on <a href="/articles/2477471">Drag-fill</a> and  <a href="/articles/1641473">Auto-filling Formulas and Formatting</a>.<br />  </p> <hr /> <h3><a id="noformula"></a>Areas where formula use is restricted</h3> <p>These fields can't contain formulas:</p> <ul> <li>Contact list fields that are being used in resource management</li> <li>System fields (for example, Modified By)</li> <li>Default columns (attachments, comments, row action indicator)</li> <li>The following fields being used for <strong>dependencies</strong> and <strong>resource management</strong>: <ul> <li>Start Date</li> <li>End Date</li> <li>Duration</li> <li>Predecessors</li> <li>% Complete</li> </ul> </li> </ul> <p>You can't create formulas in these features:</p> <ul> <li>Forms</li> <li>Update requests (Formula results can still be viewed in update requests, but they can't contain standalone formulas.)</li> <li>Reports (Formula results can still be viewed in reports, but they can't contain standalone formulas.)</li> <li>Dashboards (Formula results can be viewed in dashboards, but dashboards can't contain standalone formulas.)<a id="noedit"></a></li> </ul> <p>Formulas can't be edited from the following features in Smartsheet:</p> <ul> <li>Forms</li> <li>Update requests (Formula results can still be viewed in update requests, but the formulas can't be edited.)</li> <li>Reports (Formula results can still be viewed in reports, but the formulas can't be edited.)</li> <li>Dashboards (Formula results can be viewed in dashboards, but the formulas can't be edited.)</li> </ul> <p> </p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/226" typeof="schema:Person" property="schema:name" datatype="">Shaine Greenwood</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 01/23/2017 - 13:12</span> Mon, 23 Jan 2017 21:12:10 +0000 Shaine Greenwood 2476171 at https://help.smartsheet.com Create a Cell or Column Reference in a Formula https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula <span class="field field--name-title field--type-string field--label-hidden">Create a Cell or Column Reference in a Formula</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>When you create formulas, it's possible to include values from other cells or columns on the sheet. You do this with <strong>cell or column references</strong>. You can create a reference to an individual cell, a range of cells, or an entire column.</p> <p>It's also possible to reference data from other sheets. For information on referencing data from other sheets, see <a href="/articles/2476606">Formulas: Reference Data from Other Sheets</a>.</p> <h3>Summary of Formula Reference Types</h3> <p>Here’s a cheat sheet you can use as you build formulas of your own.</p> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <thead> <tr> <th scope="col" data-tablesaw-priority="persist"><strong>To reference this</strong></th> <th scope="col"><strong>Format it this way</strong></th> <th scope="col"><strong>Example</strong></th> </tr> </thead> <tbody> <tr> <td>Individual cell</td> <td>Column name, row number</td> <td>=Budget1</td> </tr> <tr> <td>Column name contains a space or ends in a number</td> <td>Enclose the column name in brackets.</td> <td>=[Column A]1<br /> =DAY([Q1]1)</td> </tr> <tr> <td>Absolute reference (always refers to that specific cell, row, or column)</td> <td>Type a $ symbol in front of the column name, row number or both.</td> <td>=$[Column A]$1<br /> =[Column B]$1<br /> =$[Column C]1</td> </tr> <tr> <td>Multiple, discontinuous cells</td> <td>Use a comma between cell references.</td> <td>=SUM(Budget1, Expenses4, [Projected Earnings]20)</td> </tr> <tr> <td>A range of cells in the same column</td> <td>Reference the first cell in the range, then the last cell, separated by a :(colon).</td> <td>=SUM(Budget1:Budget12)</td> </tr> <tr> <td>An entire column (including any newly added cells)</td> <td>The column name separated by a : (colon).</td> <td>=SUM(Budget:Budget)</td> </tr> <tr> <td>A range of cells across multiple columns</td> <td>Reference the upper-rightmost cell, then the lower-leftmost cell, seperated by a : (colon).</td> <td>=SUM(January1:March5)</td> </tr> <tr> <td>An individual cell, range of cells, or complete columns from another sheet</td> <td>Type the name of a previously created reference to another sheet, wrapped in curly braces.</td> <td>=COUNT({my_sheet1 Range1})</td> </tr> </tbody> </table> <h3>More Detailed Description of Reference Types</h3> <h4>Reference Individual Cells</h4> <p>While building your formula, you can <strong>select a cell</strong> to reference it and work with that cell’s data in your formula. (You can also <em>manually</em> type the column name and row number to reference the cell.)</p> <p>For example, the formula in the Inventory Value column of the following inventory management sheet will multiply the value from row 1 of the Price column to the value in row 1 of the Stock column:</p> <p><img alt="Individual reference formula" data-entity-type="file" data-entity-uuid="169da0ce-f724-4c9b-ad1e-866ef33b59d5" src="/sites/default/files/inline-images/Individual-reference-formula.png" width="425" height="89" loading="lazy" /></p> <p>The formula returns $2,994.00, the total worth of that item:</p> <p><img alt="Individual reference result" data-entity-type="file" data-entity-uuid="a098c287-bf76-4666-b80a-099b9acd2900" src="/sites/default/files/inline-images/individual-reference-result.png" width="389" height="95" loading="lazy" /></p> <h4>Reference Column Names that Have Spaces or End in Numbers</h4> <p>If a column name contains spaces or contains any special characters or numbers, you <em>must</em> enclose it in brackets to avoid ambiguity:</p> <p>=[Annual Budget]1 + [Annual Budget]2</p> <p>=[Q1]1 + [Q2]1</p> <p>=[Risk/Issue]5 + [Contingency/Mitigation]5</p> <h4>Create an Absolute Reference</h4> <p>You may have situations, such as when referencing a table with the VLOOKUP function, where you need to prevent Smartsheet from automatically updating cell references when a formula is moved or copied. To do this, create an absolute cell reference. (You can find details on the VLOOKUP function in the <a href="/function/vlookup">Function Reference</a> article.)</p> <p>To create an absolute reference, type <strong>$</strong> (a dollar sign) in front of the column name or row number in the cell reference of your formula. For example, if you move or copy the following formula, the column names and row numbers will <em>not</em> change for the cell references:</p> <p>=$[Column A]$1 * $[Column B]$1</p> <p>The following formula has absolute references to only the row numbers. If you move or copy the formula, the column references will change respectively based on the formula’s new location:</p> <p>=[Column A]$1 * [Column B]$1</p> <p>The following formula will maintain absolute references to the columns. If you move or copy the formula, the row numbers will change respectively based on the formula’s new location:</p> <p>=$[Column A]1 * $[Column B]1</p> <h4>Reference a Range of Cells in the Same Column</h4> <p>To reference a range of cells, type a <strong>:</strong> (colon) between the two cell references.</p> <p>For example, the formula at the bottom of the Inventory Value column of the following inventory management sheet will sum the values from row 1 to row 6 in the same column:</p> <p><img alt="Sum Range" data-entity-type="file" data-entity-uuid="4ab1da7e-024d-4764-abcd-092748b79518" src="/sites/default/files/inline-images/Sum-range.png" width="539" height="183" loading="lazy" /></p> <p>The formula returns $40,763.75, the total of all inventory values:</p> <p><img alt="Sum Range Result" data-entity-type="file" data-entity-uuid="750fb8ce-987a-4650-9528-25682f0a9010" src="/sites/default/files/inline-images/sum-range-result.png" width="416" height="202" loading="lazy" /></p> <h4>Reference a Whole Column</h4> <p>You can reference an entire column in a formula, including all populated cells in the column. For example, the following formula sums <em>all</em> values in the Annual Budget column and will remain updated as new rows are added to or removed from the column:</p> <p>=SUM([Annual Budget]:[Annual Budget])</p> <p>NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula. </p> <h4>Reference a Range Across Multiple Columns</h4> <p>To reference a range of values in a row across multiple columns, reference the first and last column in the row.</p> <p>For example, the formula in the Total Stock column of the following inventory management sheet will sum the values from the Stock A, Stock B, and Stock C columns on row 1:</p> <p><img alt="Sum Across Columns" data-entity-type="file" data-entity-uuid="d0c65c0a-fee3-4828-a344-2b5296420c22" src="/sites/default/files/inline-images/Sum-across-columns.png" width="551" height="81" loading="lazy" /></p> <p> </p> <p>The formula returns 998, the total stock from the three locations:</p> <p><img alt="Sum Across Columns Result" data-entity-type="file" data-entity-uuid="e85ff808-534a-4edd-a8db-232eb0a19800" src="/sites/default/files/inline-images/Sum-Across-Columns-Result.png" width="507" height="84" loading="lazy" /></p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/426" typeof="schema:Person" property="schema:name" datatype="" content="gwyneth.casazza@smartsheet.com">gwyneth.casazz…</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 07/23/2018 - 13:14</span> Mon, 23 Jul 2018 20:14:47 +0000 gwyneth.casazza@smartsheet.com 2476816 at https://help.smartsheet.com Frequently asked questions about using formulas https://help.smartsheet.com/articles/2476091-frequently-asked-questions-about-using-formulas <span class="field field--name-title field--type-string field--label-hidden">Frequently asked questions about using formulas </span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>Formulas in Smartsheet save time by calculating data automatically, combining cell contents in a sheet, and helping you keep track of dates, among other things. </p> <p>To help you get up to speed on using formulas, this article is a compilation of answers to common questions (FAQs) that customers often have as they’re ramping up.</p> <p>For specific questions about formulas, we also recommend the Smartsheet Community. The community is full of helpful, knowledgeable people with practical Smartsheet experience asking and answering questions. Someone else may have already solved the problem you’re working on.</p> <p><a href="https://community.smartsheet.com/taxonomy/term/254" target="blank">Search Community discussions about formulas now &gt;</a></p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/426" typeof="schema:Person" property="schema:name" datatype="" content="gwyneth.casazza@smartsheet.com">gwyneth.casazz…</span></span> <span class="field field--name-created field--type-created field--label-hidden">Thu, 07/07/2016 - 15:03</span> <div class="field field--name-faq field--type-entity-reference-revisions field--label-hidden field__item"> <div id="p-id-6739" class="faq"> <div class="container"> <div class="description"> <h2>Answers to Questions About Formulas in Smartsheet</h2> </div> <div class="questions"> <div class="field field--name-field-faq-item field--type-entity-reference-revisions field--label-hidden field__items"> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="canaformulareference">Can a formula reference a cell in a different sheet?</h4> </div> <div class="answer js-closed"> <p>Yes. A formula can reference a single cell, range of cells, or full columns in another sheet with <a href="https://help.smartsheet.com/articles/2476606">cross-sheet formulas</a>. </p> <p> </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="doesSmartsheetsupporta">Does Smartsheet support a VLOOKUP function?</h4> </div> <div class="answer js-closed"> <p>We do! You can find out more about this in the <a href="https://help.smartsheet.com/function/vlookup">VLOOKUP Function</a> article.</p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="howcanIget">How can I get a formula to include rows that are newly added?</h4> </div> <div class="answer js-closed"> <p>If a new row is inserted above, below, or between two rows that include the same formula, <a href="https://help.smartsheet.com/articles/1641473">the new row will inherit that formula automatically</a>.</p> <p>If you're creating a formula to use all values within a column, exclude the row references and only use the column name in the included range (e.g. =SUM([Total Hours]:[Total Hours]). This ensures that any added rows will be automatically included in the calculation.</p> <p>If you've <a href="https://help.smartsheet.com/articles/2481944">set up a column formula</a>, the formula will automatically apply to every new row that gets added to the column.</p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="cancolumnswitha">Can columns with a symbol column type be controlled by formulas?</h4> </div> <div class="answer js-closed"> <p>Yes. The character added in the formula depends on the <a href="https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column">symbols</a> you use. Refer to this list for what to add in the formula:</p> <ul> <li aria-level="1"><strong>Star, flag, and checkbox symbols: </strong>For these symbols, use <em>1</em> for on or <em>0</em> for off <ul> <li aria-level="2">Example: = IF([Status]@row = "Complete",1,0)</li> </ul> </li> <li aria-level="1"><strong>Red, yellow, and green light symbols:</strong> Use the literal values <em>Red</em>, <em>Yellow</em>, or <em>Green</em> <ul> <li aria-level="2">Example: =IF([Status]@row = "At Risk", “Red”, "Green")</li> </ul> </li> <li aria-level="1"><strong>Priority symbols: </strong>Use <em>High</em> or <em>Low</em> <ul> <li aria-level="2">Example: =IF([Status]@row = "Overdue", "High", "Low"))</li> </ul> </li> <li aria-level="1"><strong>Harvey Balls:</strong> Use <em>Quarter</em>, <em>Half</em>, <em>Three</em> <em>Quarter</em>, <em>Full</em>, or <em>Empty</em> <ul> <li aria-level="2">Example: =IF([% Complete]@row = 1, "Full", "Empty"))</li> </ul> </li> </ul> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="itypedmyformula">I typed my formula correctly, but it&#039;s displaying as text in the cell. What went wrong?</h4> </div> <div class="answer js-closed"> <p>These are three likely reasons why it’s appearing as text in a cell:</p> <ul> <li><strong>It’s missing the equal sign (=):</strong> Formulas must always start with = </li> <li><strong>Dependencies are enabled in the sheet:</strong> When dependencies are enabled on a sheet, formulas aren't allowed in these dependency-enabled columns <ul> <li>Start </li> <li>Duration </li> <li>Finish </li> <li>Predecessors </li> <li>% Complete or % Allocation: These columns automatically generate values based on the dependency settings and overwrite formulas. If you type a formula in one of these columns, it’ll be displayed as plain text rather than performing a calculation.</li> </ul> </li> <li><strong>You created the formula from a report or a mobile device:</strong> We don't currently support creating formulas from reports or from the Android and iOS apps. Any formula you enter from reports or from a mobile app will display as plain text rather than calculate or find values.</li> </ul> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="whyisn’tmyformula">Why isn’t my formula referencing % Complete working?</h4> </div> <div class="answer js-closed"> <p>Cells in sheets or reports use the decimal value, not the percentage format. This is true even for columns using percent-formatted values. </p> <p>To compare percent values or reference percent columns, refer to the table below.</p> <table border="1" cellpadding="1" cellspacing="1" style="width: 100%;" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <thead> <tr> <th scope="col" style="width: 192px;" data-tablesaw-priority="persist">Instead of this</th> <th scope="col" style="width: 188px;">Do this</th> </tr> </thead> <tbody> <tr> <td style="width: 192px;">=IF([% Complete]5 &lt; 25%, “At Risk”, “ “)</td> <td style="width: 188px;">=IF([% Complete]5 &lt; .25, “At Risk”, “ “)</td> </tr> </tbody> </table> <p> </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="canIcreateformulas">Can I create formulas in project sheet columns?</h4> </div> <div class="answer js-closed"> <p>When dependencies are enabled, columns such as Start Date, End Date, Duration, and % Complete have inherent formulas to provide project functionality. Formulas aren't allowed in these columns, as they may be overwritten. </p> <p>To work around this behavior, you can either disable dependencies on the sheet or create new columns to perform the desired calculations.</p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="myformulareferencingthe">My formula referencing the Start/End Date column isn&#039;t working. How can I fix it?</h4> </div> <div class="answer js-closed"> <p>When dependencies are enabled on a sheet, the Start Date and End Date columns have an implicit time component. The time isn't displayed in the column, but it can affect results of formulas. </p> <p>For example, you have a formula that subtracts TODAY() from the start date—which is today. You would generally expect a result of 0. What you'd actually get is a decimal value, as Smartsheet tries to perform the subtraction based on the implicit time stamp. To work around this issue, use the DATEONLY function. </p> <p>To go along with the example, the formula =[Start Date]2 - TODAY() would be revised to =DATEONLY([Start Date]2) - TODAY().</p> <p>In this case, Smartsheet only looks at the date, not the time when performing the calculation.<br />  </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="i’mseeinginaccurateresults">I’m seeing inaccurate results when I compute the difference between the Created Date and Modified Date columns. How can I fix it?</h4> </div> <div class="answer js-closed"> <p>Use the DATEONLY function. Dates in System columns are a display value and not the actual value. System columns record UTC dates, but they display your local date/time based on the UTC date. You may see results you don’t expect if you don’t use DATEONLY function. </p> <p>When you use DATEONLY, it extracts the date from the System column. Here’s a sample formula: =NETWORKDAYS(DATEONLY(Created@row), TODAY()).<br />  </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="whathappenstomy">What happens to my formulas if I use the &quot;Move to Another Sheet&quot; or &quot;Copy to Another Sheet&quot; commands to move or copy a row containing a formula to another sheet?</h4> </div> <div class="answer js-closed"> <p>Formulas won't be preserved when you use these commands to move or copy rows. You can, however, use keyboard shortcuts to <a href="https://help.smartsheet.com/articles/518318-tips-for-using-copy-and-paste#copy">copy and paste formulas</a>.</p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="whyaremynumeric">Why are my numeric values not calculating correctly? For example, when adding 1 + 12 it’s producing the result 112, instead of 13.</h4> </div> <div class="answer js-closed"> <p>Often, this calculation error occurs because one of the referenced values is being considered as a text value. </p> <p>To correct this error, check that neither of the values being referenced has a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe before it, this undesired result occurs.</p> <p>A trick to catch this is to check the numeric values formatting alignment. Often, values being considered as a text value appear left aligned in the cell, instead of the normal right alignment for numeric values.</p> <p>Numeric values appear left aligned when they’re: </p> <ul> <li>Produced by formulas, and  </li> <li>In the Primary column. </li> </ul> <p>However, these values will still be recognized as numeric values. Unless something in the formula or cell dictates otherwise. <br />  </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="i’musingaSUMIFS">I’m using a SUMIFS formula that should be returning a value, but it is returning a 0.</h4> </div> <div class="answer js-closed"> <p>If you’re evaluating the same range of data for multiple criteria, use the <a href="https://help.smartsheet.com/function/or">OR Function</a> and <a href="https://help.smartsheet.com/articles/2476491#cell">@cell parameter</a> to have the correct value returned.</p> <div> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="row" data-tablesaw-priority="persist"> <p>Item</p> </th> <th scope="col"> <p>Quantity</p> </th> <th scope="col"> <p>Cost</p> </th> <th scope="col"> <p>Warehouse</p> </th> </tr> </thead> <tbody> <tr> <th scope="row"> <p>Shirt</p> </th> <td> <p>26</p> </td> <td> <p>20.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Pants</p> </th> <td> <p>24</p> </td> <td> <p>50.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Socks</p> </th> <td> <p>10</p> </td> <td> <p>10.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Shirt</p> </th> <td> <p>18</p> </td> <td> <p>25.00</p> </td> <td> <p>B</p> </td> </tr> <tr> <th scope="row"> <p>Pants</p> </th> <td> <p>16</p> </td> <td> <p>75.00</p> </td> <td> <p>B</p> </td> </tr> <tr> <th scope="row"> <p>Socks</p> </th> <td> <p>46</p> </td> <td> <p>15.00</p> </td> <td> <p>B</p> </td> </tr> </tbody> </table> </div> <div> <p> </p> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="col" data-tablesaw-priority="persist">Formula</th> <th scope="col">Description</th> <th scope="col">Result</th> </tr> </thead> <tbody> <tr> <td> <p>=SUMIFS(Cost:Cost, Quantity:Quantity, &gt;15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))</p> </td> <td> <p>Sums the Cost of anything with a Quantity over 15 from <em>Warehouse A</em> where the item is either <em>Shirt</em> or <em>Socks</em></p> </td> <td>  <p>20.00</p> </td> </tr> </tbody> </table> </div> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="i’musingaCOUNTIFS">I’m using a COUNTIFS formula that should be returning a value, but it is returning a 0.</h4> </div> <div class="answer js-closed"> <p>If you are evaluating the same range of data for multiple criteria, use the <a href="/function/or">OR Function</a> and <a href="/articles/2476491#cell">@cell parameter</a> to have the correct value returned.</p> <div> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="row" data-tablesaw-priority="persist"> <p>Item</p> </th> <th scope="col"> <p>Quantity</p> </th> <th scope="col"> <p>Cost</p> </th> <th scope="col"> <p>Warehouse</p> </th> </tr> </thead> <tbody> <tr> <th scope="row"> <p>Shirt</p> </th> <td> <p>26</p> </td> <td> <p>20.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Pants</p> </th> <td> <p>24</p> </td> <td> <p>50.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Socks</p> </th> <td> <p>10</p> </td> <td> <p>10.00</p> </td> <td> <p>A</p> </td> </tr> <tr> <th scope="row"> <p>Shirt</p> </th> <td> <p>18</p> </td> <td> <p>25.00</p> </td> <td> <p>B</p> </td> </tr> <tr> <th scope="row"> <p>Pants</p> </th> <td> <p>16</p> </td> <td> <p>75.00</p> </td> <td> <p>B</p> </td> </tr> <tr> <th scope="row"> <p>Socks</p> </th> <td> <p>46</p> </td> <td> <p>15.00</p> </td> <td> <p>B</p> </td> </tr> </tbody> </table> </div> <div> <p> </p> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="col" data-tablesaw-priority="persist">Formula</th> <th scope="col">Description</th> <th scope="col">Result</th> </tr> </thead> <tbody> <tr> <td> <p>=COUNTIFS(Quantity:Quantity, &gt;15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))</p> </td> <td> <p>Counts anything with a quantity over 15 from <em>Warehouse A</em> where the item is either <em>Shirt</em> or <em>Socks</em></p> </td> <td>  <p>1</p> </td> </tr> </tbody> </table> </div> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="whatfunctionsworkin">What functions work in a contact column?</h4> </div> <div class="answer js-closed"> <p>Functions that can return a contact value work in contact columns. Here are some sample functions you can use:</p> <ul> <li aria-level="1" style="list-style-type:disc"><strong>Equals Contact cell:</strong> =[Assigned To]1</li> <li aria-level="1"><strong>VLOOKUP:</strong> =VLOOKUP([Assigned To]1,{Departments},2,false)</li> <li aria-level="1"><strong>PARENT:</strong> =PARENT()</li> <li aria-level="1"><strong>INDEX(MATCH()):</strong> =INDEX({Departments},MATCH([Assigned To]1,{Names},0))</li> <li aria-level="1"><strong>IF:</strong> =IF(Urgency1="High",[Urgent on call]1,[Assigned To]1)</li> </ul> <p style="line-height:1.38">Below are some specific examples of these formulas in action.</p> <p style="line-height:1.38">Sample sheet information:</p> <table border="1" cellpadding="1" cellspacing="1" style="width: 100%;" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <thead> <tr> <th scope="row" data-tablesaw-priority="persist">Row </th> <th scope="col">Project name</th> <th scope="col">Assigned to</th> <th scope="col">Date</th> <th scope="col">% Complete</th> <th scope="col">Status</th> </tr> </thead> <tbody> <tr> <th scope="row">1</th> <td>Project - E</td> <td>S Brown</td> <td>03/25/20</td> <td>30%</td> <td>Red</td> </tr> <tr> <th scope="row">2</th> <td>Outreach</td> <td>don.t@domain.com</td> <td>10/18/20</td> <td>60%</td> <td>Yellow</td> </tr> <tr> <th scope="row">3</th> <td>Smartsheet - PM</td> <td>Andy Davis</td> <td>01/06/20</td> <td>100%</td> <td>Green</td> </tr> <tr> <th scope="row">4</th> <td>Collection - INT</td> <td>Johnny Apple, S Brown</td> <td>02/12/20</td> <td>20%</td> <td>Red</td> </tr> </tbody> </table> <p><span style="color: rgb(0, 0, 0); font-family: Arial; font-size: 11pt; white-space: pre-wrap;">Given the table above, here are some examples of using Contact Formulas in a sheet:</span></p> <div> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="row" data-tablesaw-priority="persist"> <p>Formula</p> </th> <th scope="col"> <p>Description</p> </th> <th scope="col"> <p>Result</p> </th> </tr> </thead> <tbody> <tr> <th scope="row"> <p>=INDEX([Project Name]:[Project Name], MATCH("Andy Davis", [Assigned To]:[Assigned To]))</p> </th> <td> <p>Returns the value in the <strong>Project Name</strong> column for the row that contains the value <em>Andy Davis</em> in the <strong>Assigned to</strong> column</p> </td> <td> <p>Smartsheet - PM</p> </td> </tr> <tr> <th scope="row"> <p>=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "S Brown"))</p> </th> <td> <p>Counts the number of rows where the value in the <strong>Assigned to</strong> column has the value <em>S Brown</em></p> </td> <td> <p>2</p> </td> </tr> <tr> <th scope="row"> <p>=VLOOKUP("don.t@domain.com", [Assigned To]:[Date], 2, false)</p> </th> <td> <p>Returns the date. If the <strong>Assigned to</strong> column has the email <em>don.t@domain.com</em> look up and produce the value at the same row in the <strong>Date</strong> column</p> </td> <td> <p>10/18/20</p> </td> </tr> </tbody> </table> </div> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="howdoIcreate">How do I create a new contact using a formula? </h4> </div> <div class="answer js-closed"> <p>Currently, we don’t have a direct method to create new contacts with formulas. You can create  new contacts using multiple columns if the email addresses of the org follow a particular pattern. For example, first.last@domain.com.</p> <p><br /> To do this, you can have a column for <em>first.last</em> and another for <em>@domain.com</em>. Using the JOIN function can bring these two fields together into the same contact cell, and create the desired contact.</p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="willformulasthatproduce">Will formulas that produce a text value return the value as a text value in a contact cell?</h4> </div> <div class="answer js-closed"> <p>Yes. If the cells being referenced by the formula are text values and don’t create an email address, they’ll return as only a text value. </p> <p> </p> </div> </div> <div class="field__item"> <div class="question"> <h4 class="accordion-toggle js-accordion-toggle" id="canIuseformulas">Can I use formulas that output data other than contacts or text values in a contact column?</h4> </div> <div class="answer js-closed"> <p>Formulas that output data types other than contacts or text (e.g. dates, numbers, booleans, etc.) aren’t supported and will return an error code: #CONTACT EXPECTED. This is similar to how DATE columns work today. Formulas calculate to #DATE EXPECTED, if they output anything other than dates or text. Checkbox columns have a similar error: #BOOLEAN EXPECTED.</p> <p> </p> </div> </div> </div> </div> </div> </div> </div> Thu, 07 Jul 2016 22:03:06 +0000 gwyneth.casazza@smartsheet.com 2476091 at https://help.smartsheet.com Create Efficient Formulas with @cell and @row https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell <span class="field field--name-title field--type-string field--label-hidden">Create Efficient Formulas with @cell and @row</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>Optimize your formulas to improve the overall performance of your sheet and prevent having to manually reference cells in formulas.</p> <p>As your sheets get larger, having efficiently created formulas becomes more helpful to their performance. Here’s a look at two functions that will help make your formulas more efficient, @cell and @row.</p> <h3><a id="cell"></a>Use @cell to Perform Smarter Cell Calculations</h3> <p>When you want to perform calculations in formulas that look at ranges of cells, for example: SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the @cell argument in the criteria of the function. The @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range, making your formula more efficient.</p> <p>For example, using the sample data in the image below, if you wanted to sum the cost of all tasks whose due date occurred in 2014, you would create the following formula:</p> <p>=SUMIF([Due Date]1:[Due Date]5, YEAR(@cell) = 2014, Cost1:Cost5)</p> <p><img alt="@cell formula" data-entity-type="file" data-entity-uuid="779172de-53eb-40d1-9acb-1c334221ee65" src="/sites/default/files/inline-images/Formulas_Example_2_0.png" width="600" height="220" loading="lazy" /></p> <p>The result of this formula is $875.75 (the sum of the cost for all tasks whose due date occurred in 2014).</p> <p>TIP: Add the IFERROR function to your formula to ensure that you don't get an error if @cell encounters a blank cell in the formula. For example: =COUNTIF(Date:Date, IFERROR(YEAR(@cell), 0) = 2016)</p> <h3><a id="row"></a>Use @row to Improve Formula Performance</h3> <p>With @row, you can improve performance with the automatic changing of cell references in a column (for example, the status of your tasks) for a large number of rows. <br /> Let’s say we want to use a formula to change symbols in the status column of this grid automatically:</p> <p><img alt="atrow" data-entity-type="file" data-entity-uuid="816c9f2e-d8fb-4d56-a4b4-a7699a40e03e" src="/sites/default/files/inline-images/atrow_small.png" width="488" height="229" loading="lazy" /></p> <p> </p> <p>Here’s an example of a formula you might build to automate the RYG ball color change in your Status column:</p> <p>=IF(Complete3 &lt;&gt; 1, IF(TODAY() - [Due Date]3 &gt; 0, "Red", IF(TODAY() - [Due Date]3 &gt; -3, "Yellow", "Green")))</p> <p>The above formula contains multiple references to cells on row 3. Adding a row above row 3, results in Smartsheet automatically updating all of the cell references in the formula to row 4. Smartsheet would also modify formulas in every row below in the same fashion. The more formulas that need to be modified, the more your performance in Smartsheet becomes impacted.</p> <p>To possibly improve performance in your sheets, substitute the row numbers with @row in your cell references. This is how the formula would look using @row:</p> <p>=IF(Complete@row &lt;&gt; 1, IF(TODAY() - [Due Date]@row &gt; 0, "Red", IF(TODAY() - [Due Date]@row &gt; -3, "Yellow", "Green")))</p> <p>Smartsheet won’t need to modify the cell references if the row is moved—resulting in quicker sheet load and save times. You can also copy and paste this formula without having to manually change the row numbers on cell references, saving you time when you need to copy your formulas. <br />  </p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/226" typeof="schema:Person" property="schema:name" datatype="">Shaine Greenwood</span></span> <span class="field field--name-created field--type-created field--label-hidden">Fri, 04/07/2017 - 10:34</span> Fri, 07 Apr 2017 17:34:14 +0000 Shaine Greenwood 2476491 at https://help.smartsheet.com Combine (Concatenate) Text or Values from Two or More Cells with a Simple Formula https://help.smartsheet.com/articles/2476826-combine-text-two-or-more-cells-simple-formula <span class="field field--name-title field--type-string field--label-hidden">Combine (Concatenate) Text or Values from Two or More Cells with a Simple Formula</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><h3>Create a simple formula to concatenate text</h3> <p>If you want to combine text from two or more cells into a different cell (concatenate the values), you can do this with a simple formula.</p> <ol> <li>Click in the cell in which you want the result.</li> <li>Press = (the equal sign).</li> <li>Click the first cell that you want to refer to in the join and then press + (the plus sign).</li> <li>Click the second cell that you want to refer to (to join the values together) and press Enter.</li> </ol> <p>You can do this from multiple cells to join values together in a text chain or series. To get a sense of the possible ways you can combine cell data, see the examples below.</p> <p><span class="tip">As an alternative, you can use the JOIN function to combine cell values into a text string. See the Help Center article <a href="https://help.smartsheet.com/function/join">JOIN Function</a> for more information.</span></p> <h3>Concatenation formula examples </h3> <p>In the following examples, assume that [Task Name]1 contains the text "First Task" and [Task Name]2 contains "Second Task."</p> <blockquote> <p>= [Task Name]1 + " " + [Task Name]2</p> <p>Result: First Task Second Task</p> </blockquote> <p>When you join cell values, you can use delimiters to separate the values in the range:</p> <blockquote> <p>= [Task Name]1 + "-" + [Task Name]2</p> <p>Result: First Task-Second Task</p> </blockquote> <blockquote> <p>= [Task Name]1 + "," + [Task Name]2</p> <p>Result: First Task,Second Task</p> </blockquote> <p>If no delimiter value is included, the values from the range will be joined without spaces:</p> <blockquote> <p>= [Task Name]1 + [Task Name]2</p> <p>Result: First TaskSecond Task</p> </blockquote> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/426" typeof="schema:Person" property="schema:name" datatype="" content="gwyneth.casazza@smartsheet.com">gwyneth.casazz…</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 07/23/2018 - 13:44</span> Mon, 23 Jul 2018 20:44:43 +0000 gwyneth.casazza@smartsheet.com 2476826 at https://help.smartsheet.com Formulas: Reference Data from Other Sheets https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets <span class="field field--name-title field--type-string field--label-hidden">Formulas: Reference Data from Other Sheets</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>Perform calculations on data that is stored in another sheet with cross-sheet formulas. </p> <p>For example, use the VLOOKUP function to find a value from a lookup table on a different sheet you have access to. Or, use the SUMIF formula to get the sum of values you have stored on a separate sheet.</p> <p>To create cross-sheet formulas, see <a href="/articles/2476171">create and edit formulas in Smartsheet</a>.</p> <h3>Required Permissions</h3> <p>Before you learn how to work with cross-sheet formulas, review what each user can do in source and destination sheets:</p> <p> </p> <div> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="row" data-tablesaw-priority="persist"> <p> Capability</p> </th> <th scope="col"> <p>Owner</p> </th> <th scope="col"> <p>Admin</p> </th> <th scope="col"> <p>Editor</p> </th> <th scope="col"> <p>Viewer</p> </th> </tr> </thead> <tbody> <tr> <th scope="row"> <p>View and reference data in the source sheet</p> </th> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> </tr> <tr> <th scope="row"> <p>Insert a formula in the destination sheet</p> </th> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>No</p> </td> </tr> <tr> <th scope="row"> <p>Edit a reference in the formula</p> </th> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>No</p> </td> </tr> <tr> <th scope="row"> <p>Delete sheet references used in cross-sheet formulas</p> </th> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>Yes</p> </td> <td> <p>No</p> </td> </tr> </tbody> </table> </div> <p style="line-height: 1.38; background-color: rgb(255, 255, 255); margin-top: 19px; margin-bottom: 5px;"><span class="warning">If you have a permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you delete will also be removed from users who have access to the file you changed. When this happens, the data in cells with cross-sheet formulas will be affected.</span></p> <p> </p> <h3>Before you reference data </h3> <p>Ready to work with cross-sheet formulas? Keep these things in mind:</p> <ul> <li aria-level="1">A sheet can include no more than 100 distinct cross-sheet references. </li> <li aria-level="1">A reference range can include a maximum of 100,000 inbound cells.</li> <li aria-level="1">The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula. </li> </ul> <hr /> <h3>Reference Data from Another Sheet</h3> <p>You can only reference data from a single sheet. To pull data from separate sheets, create multiple references:</p> <ol> <li>In the desired cell, build your formula.<br /> For example, type =VLOOKUP(. After you type the function name and the left parenthesis, the formula help card will appear above or below your cell. </li> <li>In the help card, click <strong>Reference another sheet</strong>.<br /> <br /> <img alt="help-card" data-entity-type="file" data-entity-uuid="79ab25ff-8bc1-4bc0-995b-4a58707dba68" src="/sites/default/files/inline-images/help-card.png" width="274" height="97" loading="lazy" /><br />  </li> <li>Search for the sheet where the data exists.</li> <li>In the search results, select the source sheet.<br /> <br /> <img alt="Reference Another Sheet" data-entity-type="file" data-entity-uuid="39fdf91a-9e27-45cf-93e4-1cd7381fd7d7" src="/sites/default/files/inline-images/Reference%20Another%20Sheet.png" width="275" height="463" loading="lazy" /></li> <li>Select the cell range containing the data you want to reference, and then click <strong>Insert Reference</strong>.<br /> <br /> NOTE: Currently only a <em>contiguous</em> range is allowed.<br /> <img alt="Insert Reference" data-entity-type="file" data-entity-uuid="6ba26bfa-e110-461f-a037-cb20b28e0798" src="/sites/default/files/inline-images/Insert%20Reference.png" width="512" height="288" loading="lazy" /><br /> TIP: Select a column header to reference the whole column even if rows get added or deleted from the source. To reference multiple columns, hold the Shift key then select the first and last column you’d like to reference - all columns in between will be selected.</li> <li>Optional: In the <strong>Sheet reference name</strong> text box, type a name for your reference. If you don’t enter a name, the reference will be assigned a default name based on the sheet name and the number of times that sheet has been referenced.</li> </ol> <p>That’s it! You should now see your reference added in your formula. Here’s an example of how your formula should appear:<br />  <br /> =COUNT({my_sheet1 Range1})</p> <p><span class="note">To prevent infinite approval loops, cells that contain cross-sheet formulas or cell links will not trigger automation that changes the sheet (e.g., <strong>Move row</strong>, <strong>Copy row</strong>, <strong>Lock row</strong>, etc.). To work around this, consider using time-based or recurring automated workflows.</span></p> <h3><a a="" name="ref" id="ref">Manage Sheet References</a></h3> <p><a a="" name="ref" id="ref"> </a></p> <h4><a a="" name="ref" id="ref">View, Modify, and Delete References with the Sheet Reference Manager</a></h4> <p><a a="" name="ref" id="ref">In the <strong>Sheet Reference Manager</strong>, you can see and modify all Cross Sheet References and Cell Link References in your sheet. To open it, right-click any cell in your sheet and select <strong>Manage References…</strong></a></p> <p><a a="" name="ref" id="ref"><strong><img alt="Sheet Reference Manager" data-entity-type="file" data-entity-uuid="625befc4-ce34-4a25-a8de-5b0972813bef" src="/sites/default/files/inline-images/Sheet%20Reference%20Manager.PNG" width="1020" height="568" loading="lazy" /></strong></a></p> <p><a a="" name="ref" id="ref">You’ll be taken to the <strong>Cross Sheet References </strong>manager by default, but you can switch to the <strong>Cell Link References</strong> manager using the left panel. See </a><a href="/articles/861579-cell-linking">Cell Links: Consolidate or Maintain Consistency of Data</a> for more information about cell links.</p> <p>Use the following table for details on each of the features in the <strong>Cross Sheet References</strong> manager:</p> <p> </p> <div> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <colgroup> <col /> <col /> <col /> </colgroup> <thead> <tr> <th scope="row" data-tablesaw-priority="persist"> <p>Use this feature</p> </th> <th scope="col"> <p>To do this </p> </th> <th scope="col"> <p>Tips</p> </th> </tr> </thead> <tbody> <tr> <th scope="row"> <p>Search</p> </th> <td> <p>In the <strong>Search list…</strong> box, type keywords to find references you need to use.</p> <br />  </td> <td> <p>Use a conventional naming structure for your references such as sheet name_columns referenced.</p> </td> </tr> <tr> <th scope="row"> <p>Filter</p> </th> <td> <p>Click the <strong>All References</strong> drop-down list to set the filter to <strong>Active</strong>, <strong>Processing</strong>, <strong>Broken</strong>, <strong>In Use</strong>, or <strong>Unused</strong>.</p> </td> <td> <p>Filters help you easily find, categorize, or review references. </p> <ul> <li aria-level="1"><strong>Active</strong>: Up-to-date references</li> <li aria-level="1"><strong>Processing</strong>:  References loading updates from the source data</li> <li aria-level="1"><strong>Broken</strong>: Deleted source rows, columns, or cells</li> <li aria-level="1"><strong>In Use</strong>: References you’re using in cross-sheet formulas</li> <li aria-level="1"><strong>Unused</strong>: References that are not in any formulas in your sheet </li> </ul> </td> </tr> <tr> <th scope="row"> <p>Create</p> </th> <td> <p>To create a new reference, click <strong>+ Create</strong>. </p> </td> <td> <p>You can use this reference when you create new formulas.</p> </td> </tr> <tr> <th scope="row"> <p>Edit Reference</p> </th> <td> <p>Steps in editing references:</p> <ol> <li aria-level="1">To display the <strong>Menu </strong>icon <img src="https://lh3.googleusercontent.com/puyzhe5cE5cr9AWk9VYRCP36WsGZCpCeUbcJBi2l526kXv2dRlFU_o3qGSFkrZK7FuoLHGOIULxxbFqIWz7OKI_UwrS4grLfyExpQ11LKa-nV8ehaVAxmaePl-n5awxr6AwbWtn5=s0" />, hover over any reference in the list. </li> <li aria-level="1">Then, click <strong>Edit Reference</strong> to change the name, referenced cell range, or source sheet. </li> </ol> </td> <td> <p>If the reference you are editing is in use, all its instances in your formulas will be updated in real time.</p> </td> </tr> <tr> <th scope="row"> <p>Duplicate Reference</p> </th> <td> <p>To make a copy of a reference, click <strong>Menu </strong><img src="https://lh3.googleusercontent.com/puyzhe5cE5cr9AWk9VYRCP36WsGZCpCeUbcJBi2l526kXv2dRlFU_o3qGSFkrZK7FuoLHGOIULxxbFqIWz7OKI_UwrS4grLfyExpQ11LKa-nV8ehaVAxmaePl-n5awxr6AwbWtn5=s0" /> &gt; <strong>Duplicate Reference</strong>. </p> </td> <td> <p>The name of the duplicate reference will be <em>Copy of_original reference name</em>.</p> </td> </tr> <tr> <th scope="row"> <p>Delete Reference</p> </th> <td> <p>To delete a reference, click <strong>Menu </strong><img src="https://lh3.googleusercontent.com/puyzhe5cE5cr9AWk9VYRCP36WsGZCpCeUbcJBi2l526kXv2dRlFU_o3qGSFkrZK7FuoLHGOIULxxbFqIWz7OKI_UwrS4grLfyExpQ11LKa-nV8ehaVAxmaePl-n5awxr6AwbWtn5=s0" /> &gt;  <strong>Delete Reference</strong>. </p> </td> <td> <p>If the deleted reference is in use, the formulas may return #INVALID REF errors.</p> </td> </tr> </tbody> </table> </div> <p> </p> <h3>Edit a Reference Directly in your Formula</h3> <p>You can edit a reference directly in your formula without opening the Sheet Reference Manager. </p> <p><span class="note">You must have Editor-level sharing or greater permissions on a sheet to edit the reference. You must also have Viewer-level sharing permissions or greater on the sheet that contains the data you want to reference.</span></p> <ol> <li>Select the cell that contains your formula. A summary of the formula appears in the left or right of the cell.<br /> <br /> <img alt="reference-summary" data-entity-type="file" data-entity-uuid="38180db5-ee12-4f48-ac7e-200740c24b8c" src="/sites/default/files/inline-images/edit-reference.png" width="314" height="60" loading="lazy" /><br />  </li> <li>Click the name of the reference that you’d like to edit. </li> </ol> <p>Once you’re in the <strong>Reference Another Sheet</strong> dialog, you can change the reference name, or choose a new sheet or cell range. <br />  <br /> When you edit a reference, all its instances in the sheet will update in real time.</p> <h3>Tips for working with references</h3> <ul> <li aria-level="1"><strong>Set your reference data.</strong> Once you choose a sheet and see the range picker, you can select the individual cell, cell range, or complete columns you’d like to reference.</li> <li aria-level="1"><strong>Don’t include any data you don’t need to see in the destination sheet. </strong>All information in the reference range is available and may appear in the destination sheet. </li> <li aria-level="1"><strong>Ensure new rows are in the reference range.</strong> Rows added below a referenced range will not be included. To include all rows, select the columns as the reference.</li> <li aria-level="1"><strong>Use multiple references</strong> to other sheets in the same formula, if needed.</li> <li aria-level="1"><strong>Edit the formula to use a reference you created.</strong> You don’t have to open the <strong>Reference Another Sheet</strong> dialog to use an existing reference. In the new formula, add braces <strong>{ } </strong>; then, type or paste the reference name in them. </li> <li aria-level="1"><strong>References are unique to the sheet where they were created.</strong> You have to make new references for each sheet.. </li> <li aria-level="1"><strong>Open the Reference Another Sheet dialog through the in-line help card.</strong> Hover over the reference name while editing a formula. Then, click the link in the <strong>Edit Reference</strong> section.</li> <li aria-level="1"><strong>Organize or clean your references.</strong> Edit <strong>Broken </strong>references, or remove them from your formula/s. Delete <strong>Unused </strong>references too, if you don’t plan to use them.</li> <li aria-level="1"><strong>Add an Unused reference in your formula, if necessary.</strong> Type a formula in a cell. Then, add braces and put the <strong>Unused </strong>reference’s name in them.</li> </ul> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/226" typeof="schema:Person" property="schema:name" datatype="">Shaine Greenwood</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 09/25/2017 - 15:01</span> Mon, 25 Sep 2017 22:01:38 +0000 Shaine Greenwood 2476606 at https://help.smartsheet.com Formula Error Messages https://help.smartsheet.com/articles/2476176-formula-error-messages <span class="field field--name-title field--type-string field--label-hidden">Formula Error Messages</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>A formula will return an error when Smartsheet expects different elements from the formula than what are provided. Below is a list of formula error messages, their causes, and the resolutions you can implement to fix the errors.</p> <hr /> <h3><a id="blocked"></a>#BLOCKED</h3> <h4>Cause</h4> <p>The calculation is blocked because at least one of the cells referenced by the formula has an error.</p> <h4>Resolution</h4> <p>Determine which cell referenced by this formula contains an error, which will be more descriptive of the problem.</p> <hr /> <h3><a id="booleanexpected"></a>#BOOLEAN EXPECTED</h3> <h4>Cause</h4> <p>The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.</p> <h4>Resolution</h4> <p>Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example =TODAY() + "" will allow you to enter today's date into a text column.</p> <hr /> <h3><a id="calculating"></a>#CALCULATING</h3> <h4>Causes</h4> <p>The formula engine is still calculating on the backend.</p> <ul> <li>This can often mean you are experiencing a slowness error due to network conditions or heavy browser usage.</li> <li>It could also indicate that you are using a larger formula and/or the formula is referencing a cell that is referencing another cell via a formula or cell linking.</li> </ul> <h4>Resolution</h4> <p>Often this error corrects itself once the calculation is complete. </p> <p>If the error doesn’t correct itself, slowness on a sheet can be influenced by several different factors. Some of the main factors:  </p> <ul> <li><strong>Browser speed</strong>—browser speed can be one of the main contributors of slowness when loading, navigating, and saving a sheet. In our tests, we have found Smartsheet to perform best using Google Chrome. If it is an option or when troubleshooting, try using this browser to see if it improves your response times.</li> <li><strong>Advanced functionality</strong>—widespread use of formulas, cell-linking, and conditional formatting can contribute to slower load times. If your sheet starts to slow down, try disabling conditional formatting rules to see if this is contributing to the issue. Also, consider getting rid of formula columns or cell-links which might no longer be needed. </li> <li><strong>Sheet size</strong>—as sheets get larger, performance may decrease. When appropriate, move rows (more on <a href="/articles/504748">Moving Rows)</a> to an archival sheet.</li> </ul> <blockquote> <p>If you are utilizing cross-sheet references you may wish to also confirm the sheet isn’t approaching the 100,000 cell reference limit.</p> </blockquote> <hr /> <h3><a id="circularreference"></a>#CIRCULAR REFERENCE</h3> <h4>Cause</h4> <p>The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.</p> <h4>Resolution</h4> <p>Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.</p> <hr /> <h3><a id="contactexpected"></a>#CONTACT EXPECTED</h3> <h4>Cause</h4> <p>A formula in a Contact List field has returned a datatype other than text or contacts.</p> <h4>Resolution</h4> <p>Formulas in Contact List fields may only return text or contact datatypes. Adjust the formula to return one of those datatypes or move the formula into another type of field (Text/Number, Date, Dropdown List, Checkbox, or Symbol)</p> <hr /> <h3><a id="dateexpected"></a>#DATE EXPECTED</h3> <h4>Cause</h4> <p>The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.</p> <h4>Resolution</h4> <p>Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example =TODAY() + "" will allow you to enter today's date into a text column.</p> <hr /> <h3><a id="dividebyzero"></a>#DIVIDE BY ZERO</h3> <h4>Cause</h4> <p>Your formula attempted to divide an amount by zero (e.g. 4 / 0 provides a divide by zero error).</p> <h4>Resolution</h4> <p>Use an IFERROR to return a different value in case the formula were to divide by zero. For example,</p> <p>=IFERROR(Value4 &lt;&gt; 0, 100/Value4, "")</p> <hr /> <h3><a id="error"></a><a id="imbalanced"></a>#IMBALANCED</h3> <h4>Cause</h4> <p>The parentheses in your formula do not agree. The number of open parentheses is not the same as the number of closed parentheses.</p> <h4>Resolution</h4> <p>Check your formula for extraneous parentheses—add additional parenthesis or remove them as needed.</p> <hr /> <h3><a id="incorrectargumentset"></a>#INCORRECT ARGUMENT SET</h3> <h4>Cause</h4> <p>This error is presented under the following circumstances:</p> <ol> <li>For functions that take two ranges: The range sizes don’t match for the function.</li> <li>The function is missing an argument.</li> <li>There is an extra function in the argument.</li> </ol> <h4>Resolution</h4> <p>Correct the range size or arguments, adding or removing arguments in the formula.</p> <hr /> <h3><a id="invalidcolumnvalue"></a>#INVALID COLUMN VALUE</h3> <h4>Cause</h4> <p>The formula contains or references a data type that is inconsistent with the column type where it is inserted. For example, this MAX formula is placed in a Text/Number column and references other values in the Date column...</p> <p><br /> =MAX([Due Date]1, [Due Date]52)</p> <p><br /> ...If the column that contains the formula is not a Date column type, the latest date in the range cannot be returned. (See the <a href="/function/max">MAX Function</a> Help article for more information on how the MAX function works.)</p> <h4>Resolution</h4> <p>Make one of the following adjustments to your sheet:</p> <ul> <li>Type the formula in a different column, one where its type aligns with the type of value that the formula is expected to return.</li> <li>Right-click the column header at the top of the column and select Edit Column Properties. Change the column to a type that aligns with the type of value that the formula is expected to return.</li> <li>Add IFERROR to your function such that it returns a different value if it encounters the #INVALID COLUMN VALUE error, for example: =IFERROR(“Not a number!”, MAX([Due Date]1, [Due Date]5))</li> <li>Wrap your formula in a function that can convert it to the expected data type. For example: =WEEKNUMBER(MAX([Due Date]1, [Due Date]5) when placed in a Text/Number column returns a number that corresponds with the highest date’s week number out of a 52 week period. (<a href="/function/weeknumber">More on WEEKNUMBER here.</a>)</li> </ul> <hr /> <h3><a id="invaliddatatype"></a>#INVALID DATA TYPE</h3> <h4>Cause</h4> <p>The formula contains or references an incompatible data type, such as =INT("Hello")</p> <h4>Resolution</h4> <p>Make sure the formula references the correct data type.</p> <hr /> <h3><a id="invalidref"></a>#INVALID REF </h3> <h4>Cause</h4> <p>A reference name to another sheet (in curly braces) doesn’t exist as a reference to another sheet.</p> <h4>Resolution</h4> <p>Either change the name within the curly braces to be that of an already created reference to another sheet, or create a new reference to another sheet.</p> <p>See <a href="/articles/2476606">Formulas: Reference Data from Other Sheets</a> for more information on this process.</p> <hr /> <h3><a id="invalidoperation"></a>#INVALID OPERATION</h3> <h4>Cause</h4> <p>Operators (see <a href="/articles/2476171">Create and Edit Formulas</a> for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...</p> <p>=IF(Quantity1 =&lt; 25, "Low Stock")</p> <p>...has the "less than or equal" to operator in the wrong order; the correct order being &lt;= to make the formula =IF(Quantity1 &lt;= 25, "Low Stock")</p> <h4>Resolution</h4> <p>Check all operators to make sure they're not mistyped and are supported by Smartsheet. See <a href="/articles/2476171">Create and Edit Formulas</a> for details on acceptable operators. The most common cause of this is typing &lt;= (less than or equal to) and &gt;= (greater than or equal to) operator combinations in the wrong order.</p> <hr /> <h3><a id="invalidvalue"></a>#INVALID VALUE</h3> <h4>Cause</h4> <p>The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character "H" in the text string "Hello" has a starting position of 100 characters...</p> <p>=FIND("H", "Hello", 100) </p> <p>...Since the string "Hello" is 5 characters long, it isn't possible to have FIND start looking for the character "H" at 100 characters in. Only the numbers 1-5 can be accepted.</p> <h4>Resolution</h4> <p>Make sure that all number arguments are within the range that the function expects.</p> <hr /> <h3><a id="nestedcriteria"></a>#NESTED CRITERIA</h3> <h4>Cause</h4> <p>You’ve nested criteria in a SUMIF or COUNTIF function.</p> <h4>Resolution</h4> <p>Use SUMIFS or COUNTIFS instead, as you can include multiple criteria without nesting.</p> <hr /> <h3><a id="nomatch"></a>#NO MATCH</h3> <h4>Causes</h4> <ul> <li>The VLOOKUP function hasn't found a result.</li> <li>There is not a number within the range that is greater than or equal to the search_value in the VLOOKUP function.</li> </ul> <h4>Resolution</h4> <p>Adjust the search_value or lookup_table of the VLOOKUP function such that it finds a result. (More on <a href="/function/vlookup">VLOOKUP</a>.)</p> <hr /> <h3>#NUMBER EXPECTED</h3> <h4>Cause</h4> <p>The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.</p> <h4>Resolution</h4> <p>Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example</p> <p>=TODAY() + "" will allow you to enter today's date into a text column.</p> <hr /> <h3><a id="overflow"></a>#OVERFLOW</h3> <h4>Cause</h4> <p>The formula encountered or computed a value which exceeds the supported numeric range. For numeric values the range is -9007199254740992 through 9007199254740992. For WORKDAY calculations, maximum number of days is one million.</p> <h4>Resolution</h4> <p>In most cases, this error is caused by inaccurate or incomplete cell references. Check your formula and make necessary changes.</p> <hr /> <h3><a id="permissionerror"></a>#PERMISSION ERROR</h3> <h4>Cause</h4> <p>The formula references data from another sheet (in curly braces), but no one has sufficient sharing permissions to the sheet that contains the formula as well as the source sheet containing the data being referenced.</p> <h4>Resolution</h4> <p>Make sure that at least one person is shared to both the sheet containing the formula with Editor-level permissions or greater, as well as being shared to the sheet containing the data with Viewer-level permissions or greater.</p> <hr /> <h3><a id="ref"></a>#REF</h3> <h4>Cause</h4> <p>The formula references a cell which does not exist, possibly due to a row/column deletion.</p> <h4>Resolution</h4> <p>Your formulas will need to be updated manually.</p> <hr /> <h3><a id="unparseable"></a>#UNPARSEABLE</h3> <h4>Cause</h4> <p>The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.</p> <h4>Resolution</h4> <p>Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").</p> <hr /> <h3><a id="unsupportedcrosssheetformula"></a>#UNSUPPORTED CROSS-SHEET FORMULA</h3> <h4>Cause</h4> <p>The CHILDREN, PARENT, or ANCESTORS functions are referencing data from another sheet (in curly braces), which is not supported.</p> <h4>Resolution</h4> <p>Remove the reference from that function. You can create a reference to another sheet and manually select the desired cells. For more information on creating references to other sheets, see <a href="https://help.smartsheet.com/articles/2476606">Formulas: Reference Data from Other Sheets</a>.<br />  </p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/226" typeof="schema:Person" property="schema:name" datatype="">Shaine Greenwood</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 01/23/2017 - 15:33</span> Mon, 23 Jan 2017 23:33:51 +0000 Shaine Greenwood 2476176 at https://help.smartsheet.com Use Formulas to Perform Calculations With Dates https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates <span class="field field--name-title field--type-string field--label-hidden">Use Formulas to Perform Calculations With Dates</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>You can place a formula in one cell (formatted with a Date column type) that adds or subtracts numbers from dates in other cells. Numbers are treated as days, unless date functions are used.</p> <p>The following table contains examples of using dates in formulas. (The dates in the table below are in mm/dd/yy format.) </p> <table border="1" cellpadding="1" cellspacing="1" class="tablesaw tablesaw-stack" data-tablesaw-mode="stack" data-tablesaw-minimap=""> <thead> <tr> <th scope="col" data-tablesaw-priority="persist"> </th> <th scope="col">Date</th> <th scope="col">Formula</th> <th scope="col">Description</th> <th scope="col">Result</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>5/19/17</td> <td>=Date1 - 5</td> <td>Subtracts 5 days from the date</td> <td>5/14/17</td> </tr> <tr> <td>2</td> <td>12/10/17</td> <td>=Date2 + 5</td> <td>Adds 5 days to the date</td> <td>12/15/17</td> </tr> <tr> <td>3</td> <td>1/20/18</td> <td>=DATE(YEAR(date12), MONTH(date12) + 1, DAY(date12))</td> <td>Add a month to the date with use of the DATE, YEAR, MONTH, and DAY functions</td> <td>2/20/18</td> </tr> </tbody> </table> <p> </p> <p>More information on date functions can be found in the <a href="/functions">Functions List. </a></p> <p>NOTES:</p> <ul> <li aria-level="1" style="list-style-type:disc">You can't place formulas in date columns being used for dependencies. See Enabling Dependencies &amp; Using Predecessors for more on dependencies.</li> <li aria-level="1">The month and day value used in the DATE Function are restricted. For more information, see <a href="https://help.smartsheet.com/function/date">Date Function</a>.</li> </ul> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/426" typeof="schema:Person" property="schema:name" datatype="" content="gwyneth.casazza@smartsheet.com">gwyneth.casazz…</span></span> <span class="field field--name-created field--type-created field--label-hidden">Fri, 08/03/2018 - 16:57</span> Fri, 03 Aug 2018 23:57:40 +0000 gwyneth.casazza@smartsheet.com 2477601 at https://help.smartsheet.com Reference Children, Parents, and Ancestors with Hierarchy Functions https://help.smartsheet.com/articles/2476811-reference-children-parents-ancestors-hierarchy-functions <span class="field field--name-title field--type-string field--label-hidden">Reference Children, Parents, and Ancestors with Hierarchy Functions</span> <div class="clearfix text-formatted field field--name-body field--type-text-with-summary field--label-hidden field__item"><p>You can use hierarchy in Smartsheet to indent or outdent groups of cells to better show relationships between groups of data. Hierarchy <strong>functions</strong> allow you to include cells in other functions based on their indent level in a sheet. You can place a hierarchy function inside of another function, for example, to automatically reference all indented child cells underneath a parent, even as new child rows are added to the parent row. For example, you could use the following formula to sum all of the child values in a particular column:</p> <p>=SUM(CHILDREN())</p> <p>For more information about how to use the functions that allow you to work with hierarchy, see  the following in the Smartsheet Function reference:</p> <ul> <li><a href="/function/children">CHILDREN Function</a> Reference all child cells underneath a parent.</li> <li><a href="/function/parent">PARENT Function</a> Reference the direct parent cell of a child cell.</li> <li><a href="/function/ancestors">ANCESTORS Function</a> Reference all parent cells to a child cell.</li> </ul> <p>You must have Editor- or Admin-level sharing permissions or be the sheet Owner to create and edit formulas in a sheet. Editors can only create and edit formulas in unlocked cells. Details about locked cells can be found in our <a href="/articles/522077">Locking and Unlocking Columns and Rows</a> article.</p> <p><span class="note">Hierarchy functions are not supported in cross-sheet formulas. Attempting to use the <strong>CHILDREN</strong>, <strong>PARENT</strong>, and <strong>ANCESTORS </strong>functions in a cross sheet formula will result in an <strong>#UNSUPPORTED CROSS-SHEET FORMULA</strong> error in the cell containing the formula. For more information, see <a href="/articles/2476176">Formula Error Messages</a> and <a href="/articles/2476606">Formulas: Reference Data from Other Sheets</a>.</span></p> </div> <span class="field field--name-uid field--type-entity-reference field--label-hidden"><span lang="" about="/user/426" typeof="schema:Person" property="schema:name" datatype="" content="gwyneth.casazza@smartsheet.com">gwyneth.casazz…</span></span> <span class="field field--name-created field--type-created field--label-hidden">Mon, 07/23/2018 - 12:55</span> Mon, 23 Jul 2018 19:55:54 +0000 gwyneth.casazza@smartsheet.com 2476811 at https://help.smartsheet.com