-
Formula calculating Status on Parent and Child Rows
Right now, the overall project status formula displays as "Complete" only if all the checklist items are marked as "Completed". We have been asked to update the formula so that it if all checklist items are marked "Completed" OR "Cancelled" the overall project status will be "Complete". The "Tasks Not Needed" references a…
-
Create a summary sheet
I want to create a sheet that computes all types of summary info from various other sheets. I have sheets called ProjectA, ProjectB, ProjectC. I also have a sheet called Summary-Info. In Summary-Info I want to create rows of metrics, such as Count of number of rows in ProjectA with Status="Done" Count of number of rows in…
-
Permit formulas in columns with the "Restrict" value toggle enabled
I have columns that are set to take certain types of data, and they contain formulas that fill the cells with values permissible within that data type. For instance, there's a field where a formula fills in one of several options based on a cell reference, or the user can manually select one of the options from a dropdown…
-
Counting Columns
Is there a formula that allows me to count the number of columns in a sheet (to include in a sheet summary/)
-
Rolling 12 Months and Rolling 4 Quarters Formulas for Dashboard Charts
Hello, I have a metrics sheet that has static start and end dates for each month of 2023 and 2024. The requirements changed for the dashboard and management wants the dashboard charts to show data for a rolling 12 months and a rolling 4 quarters. This screenshot shows the column setup and formula I have for calculating the…
-
Replacing all special characters in form query string parameters with a formula
Hello everyone! I am excited to be speaking at the "Leveraging the Query String in Forms" session at Engage 23! I wanted to share a formula that a customer and I developed to replace any special characters that could show up in cell when you are building a URL to a form with query string parameters using a formula. This…
-
How to do lookup for multi select dropdown cell to return results for all selected items in cell?
Hi I'm trying to do a lookup or index/match or whatever that can search all values in a dropdown list cell with multiple values and return multiple values. Sheet 1 screenshot (yellow highlight is where I want formula to be): Sheet 2 screenshot (contains table of lookup values): I'm trying formula from this question from…
-
Dynamically referencing parent task row in COUNTIF/COUNT formula for child task completion ratio?
Hello Smartsheet Community, I'm trying to calculate the ratio of completed child tasks for a specific parent task in my sheet. Currently, I have a formula that works when I manually specify the parent task's row number: =COUNTIF(CHILDREN([Task Complete?]70), 1) / COUNT(CHILDREN([Task Complete?]70)) However, I want to make…
-
Project Status Formula and Checklist items
We have a project checklist template that is provisioned via Control Center. There are hidden columns (the "Task Not Needed" referenced in the formula) that show only the checklist for the Project Type selected in intake - the tasks that are not supposed to appear are checked because all the project-specific checklists…
-
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…
