-
Formula to convert date (e.g., 03/13/22) to text/number field (03/13/22) in same sheet?
A sheet that has a Dynamic View includes a date field (Start Date) that Dynamic Viewers need to use as a filter. Since users cannot filter by date fields in Dynamic View, I've added a helper column (text/number) in the sheet but cannot find a formula to pull the dates in the Start Date column into the helper column so…
-
I want a formula that specifies the lowest child of an ancestor, parent, child hierarchy.
I am using the Hierarchy formula, =COUNT(ANCESTORS()), to label my project tasks. A lot of the child tasks have tasks below that, going to a hierarchy of 5. I want to create metrics sheet that shows the estimated completion date for the lowest level child. Because the levels vary by task, I cannot choose a specific…
-
Average if multiple criteria
I am looking to see if there is a way to have multiple average ifs to show a visual for the current working project So IF status = working, and assigned to Group 4, I am looking to show average # of days: 1.) Submitted to triage 2.) Triage to start 3.) Start to Root cause 4.) Root cause to council approval 5.) Council…
-
Median & Sum of Multiple Columns in Date Range
Hello, I am attempting to write a formula to display the median of 6 columns, only if they contain data within the last 7 days per the date column. I also need to display the sum of the same 6 columns if they contain data within the last 7 days per the date column. I've tried what seems to be an endless variety of formulas…
-
IF, IFERROR, SEARCH nested formula help
Hi team - Looking for help in either fixing the below formula or creating a new formula. Goal behind the logic: Be able to use this cell to confirm if another column,same row (Level) has the abbreviation "Mgr." If it doesn't add a dash "-", full stop. If it does, we need to search another column,same row (rep region) for…
-
SUMIFS Question for adding a criteria for a column sum function
Hello! I am working on creating a travel budget dashboard for my team. I threw in some random numbers but essentially I am creating this data sheet to reference my main sheet where I house all travel budget requests. In my other sheet I treat as a data repository of all requests I receive. The above {cost} and {Dom or Int}…
-
Health Status/Rating calculated by Removing Specific Task names
Hello Smartsheet Community, My team and I are looking to improve a formula that helps to roll-up a score/health status of all of items within an activity plan, which is our RAID Log (Risk, Action, Issue, and Decision). We have a formula that calculates an aggregation/overall rating of all of the risks we have identified,…
-
Need help showing child row statuses in parent row status
Hi! I need help creating a formula that shows the child row statuses in the parent row. Is the below possible to build into a formula that I can pop into the parent row? Show “Not Started” if all child rows say “Not Started” Show “On Track” if any of the child rows say “On Track” Show “With Client” if any of the child rows…
-
IF CONTAINS INDEX MATCH Formula
I would like to be able to create a report where I can see Activity A, Finish (of activity A), Report A, and Finish (of Report A) in separate columns. I don't want to use group functionality in Row Report as it becomes messy when pulling it multiple activities from 50+ sheets. Hence, I would like to create two additional…
-
Possible to copy data to cell if only one of multiple columns contain information
Greetings, Is there a formula that can be used to copy the text from a range of columns only if there is information in that column? For example, I have Columns [Name A], [Name B], [Name C]. Only one of these columns will be filled at the any time. If one of those columns has information, copy that information into Column D
