-
MAX COLLECT where one range has multiple values
Hi, I am using MAX COLLECT to extract from a sheet the most recent date of an event, based on a few criteria. One of the columns can have multiple values (as the source is a multiple value drop down). The current formula is =MAX(COLLECT({Date}, {Blank}, $[Primary Column]$2, {Section}, [S1]1)) where: {Date} = date in source…
-
Counting Checkboxes + Date Range
Hi, I am currently trying to count the amount of Covid Cases and have it report every Wednesday. For example, I need the total number of Covid Cases from 1/5/22-1/12/22 (so the report to show a weeks worth every Wednesday). I have it setup that if someone is Covid+, they check a box. I have the formula to count the Covid+…
-
SUMIF row is in the next 5 working days
Hello, I am trying to create a formula that counts the total size (EQA) of the jobs that are due in the next 5 working days. I am using this formula but it's coming up with 0 =SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], ISDATE(1), [Delivery Date]:[Delivery Date], NETWORKDAYS(TODAY(), TODAY(7), 0)) Any help on this…
-
Sequential project number based on team and product name?
Hello Smart community, Trying to achieve what seems many are also working on: sequential project numbers. Below, the team is a drop down with options, the product name is manual and the result is what's below. Trying to achieve the following: UXR00 ending with a sequential number, e.g. 001... n If same team, increase 1,…
-
Trying to get a date range to appear as a text month
Hi i have a audit planner and i need a actual date field for another formula in the sheet but would like another column to show the auditees when their audit month has been allocated, therefore i would like a formula to convert the date 31/01/2022 from column A to January 2022 in Column B. Any ideas?
-
Conditional formatting based off of blank date cell
Hello! I have tried to solve this problem with a lot of experimentation and reading many posts in the Smartsheet Community. Unfortunately, I just can't get it to work. I am trying to change the color of the circle symbol of a status field based off of the value in a date field (Target Date). I want the symbol to turn gray…
-
Collect and HAS Functions
I need to find the average dollar amount across a range only if the fiscal year IS 2018 and a multiselect column HAS a given value =AVG(COLLECT([Funding Amount]:[Funding Amount], Subsector:Subsector, HAS(@cell "Cell Therapy"), [Fiscal Year]:[Fiscal Year], "2019")) Is what I'm trying to do feasible?
-
Formula for RYG
I'm looking for a formula that would turn the cell within the Status Column R or G based on if specific cells within the row are filled out vs blank. For example, all identified cells are completed so it's green or one cell is missing data so it's Yellow, and none of the identified cells have info so it's Red.
-
First Monday of month start date in Project plan
Hi everyone. is there a way for me to have the system automatically find the first Monday of the month showing in the the project plan start date without me having to change it every month?
-
I need help with my summary sheet using COUNTIFS
I have a Summary Sheet that is linked to a Dashboard and i keep getting errors with my formulars. I am using the submission date and completion date from a tracking sheet as my reference sheet Any help on the right formulas to use to get the summaries would be appreciated.