-
join collect
I have a formula that returns as expected =IF(ISBLANK([unique.invoice]@row), "", JOIN(COLLECT(collect:collect, [Invoice No.]:[Invoice No.], [unique.invoice]@row), CHAR(10))) but I tried to replicate the formula to join collect another column and its not working =IF(ISBLANK([unique.invoice]@row), "",…
-
Master Schedule 'delayed' calculation
I have a 'delayed' column to calculate the number of days delayed for a specific task and use that to create a table of delayed tasks on my dashboard. The calculation works BUT it also includes any subsequent tasks that have the initial delayed task as a predecessor. How can I filter the report which displays the delayed…
-
Week number comparison
=IF($[STUDY NUMBER]@row = "", "", IF($[STUDY NUMBER]@row = "EMPTY", "", IF(AND(WEEKNUMBER($ARRIVAL@row) <= [WK 39 SEP 26]$1, WEEKNUMBER($[END DATE]@row) >= [WK 39 SEP 26]$1), $[# FLOWERS]@row, IF(AND(YEAR($ARRIVAL@row) <= 2021, WEEKNUMBER($[END DATE]@row) >= [WK 39 SEP 26]$1), $[# FLOWERS]@row,…
-
Is it possible to evaluate multiple predecessors and match to a cell value in another column?
I need to correlate a predecessor row to a work order number in a different column within the same sheet. There can be multiple predecessors. Is there a way to have a formula evaluate multiple predecessors and show a corresponding work order number? Here is a screenshot, I'm trying to figure out if a formula can be used to…
-
Update all cross references in formulas across row
I'm attempting to build a summary sheet that pulls the same milestone names status (column 9 in reference sheet) (maybe on different rows per sheet hence the vlookup) from different cross referenced sheets per row to display on a dashboard. Right now my vlookup works but there are 20 milestones per row that need to be…
-
Formula to trigger red flag based on conditional formatting rules?
Is there a formula that will mark a flag red based on conditional formatting of other columns in a row? For example, I've set up multiple conditional formatting rules within a tracker sheet. I'd like to have a checkbox column with a flag that would turn red if some of those conditions were met using conditional formatting…
-
Separating out a date by Quarter and Year
Hello, I have an End Date column (which is formatted as mm/dd/yyyy). I need a formula to show as Q1-2022 (example) for any given date in the column. In excel, I was able to find a formula that works but transferring it to Smartsheet I receive an UNPARSEABLE error message. That formula is: ="Q"&INT((MONTH(End…
-
COUNTIFS & AND + <>
Hello, I appreciate your help in fixing this formula. I want to exclude from counting any blank cell for one column & any cell with "Center" from another column. The one for excluding "Center" is not working. =COUNTIFS(AND([Deliverable(s)]3:[Deliverable(s)]137, <>"", [Responsible Party]3:[Responsible Party]147,…
-
Looking to set a Date Field based on contents of another field.
We have a sheet with a list field identifying the target fiscal year and quarter that we want an activity to start eg. FY 23 Q1, FY 23 Q2 etc. This sheet also has been set up to work as a Gantt view with Start, Finish, and Duration as well. I'd like to set the start date automatically based on the contents of the FY/Q…
-
Workday formula not working
I have a date linked in from a separate schedule smartsheet and want to use the WORKDAY formula to calculate 10 business days from that date, but am getting an error and can't figure out what I'm doing wrong. The following formula is returning an #INVALID DATA error: =WORKDAY([IDTF START]1, 10) What am I doing wrong?
