-
How to flag the next 4 rows with no children
Hey all, I'm stuck on a formula for a project task sheet. In the below picture, I have the Children column, which shockingly calculates the number of children that row has. Basically, if it has no children, it is a task. If it has children it is a parent/summary row. I want to flag the next 4 tasks (0 children) after the…
-
SUMIFS not working with additional field added
Hello, I'm trying to do a SUMIFS Formula for a month. =SUMIFS({Period Month}, "January", {cuscode}, Category@row, {extprice}) I'm getting incorrect argument. Previously I did this formula for the total and it worked. =SUMIF({cuscode}, Category@row, {extprice}) Not sure why the modification isn't working? Amy
-
Using RYG to determine health status of progress made
This is a question I am piggy backing on.. Automating a Roll up for RYG Symbols for Health — Smartsheet Community I am also trying to use a similar formula but based on % Scheduled Elapsed, % Progress delayed and % completed. I'm using a template created but trying to make a new workspace that does pull from a different…
-
CountIF and OR Formula
Hello, I'm trying to construct a COUNTIFS formula which is currently: =COUNTIFS({Arrival Date}, {Arrival Date} <= DATE(2023, 7, 31), {Arrival Date}, >=DATE(2023, 7, 1), {Status}, "Provisional") This works and returns a value but I also want it to include {Status} of "Confirmed" as well as "Provisional". I've tried:…
-
Help with multiple OR criteria to return data from 1 of 4 cells to another cell
I have 4 options (logic added to them) for members to select where they work but need their specific clinic to be added to one main Clinic column and am looking for the best formula to have it automatically update to the column. If the Market equals one of the 4 area clinics then it return the correct clinic name for their…
-
Using CONTAINS with JOIN(COLLECT)
Hello! I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work. The existing (working) formula is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, "1", {RAID Log Category}, "Issue", {RAID Log Top 10 Priority}, [Priority Name]@row), ", "). The formula I've come up with to add…
-
Display cell contents in another cell based on dropdown selection in a column.
Hello all, I have recently started using smartsheet for H&S audits. I have compiled an audit report as per the image. The report counts the number of "Yeses", "Improves", "Nos" and "Not Applicables/Not Checked". The sum is displayed in the table at the bottom right corner. Scoring is also calculated based on a formula.…
-
Help with calculated columns / data manipulation to display in dashboards
It's quite a long one, so bear with me! We have a process where Program Managers provide a status update (among other things) on their programs every 2 weeks (On Track, At Risk, Severe Risk etc.) A program could be sitting at "At Risk" for a 12 months, and this is what I want to display on my dashboard. I want to know what…
-
Pulling a list of users who are taking a course
I need assistance with creating an automated report for each course in my spreadsheet. The spreadsheet contains information about courses and learners. Instead of manually creating reports for over 100 courses, I want to establish a link from each course entry that leads to a report or filtered spreadsheet listing the…
-
Countifs with and
I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet). My formula before factoring…