-
Formula to add days to a date
I have a dropdown column for PM Frequency (1 month, 3 month, 6 month etc...) and date columns for last PM and next PM. I want to add the appropriate number of days to last PM to generate a date for next PM based on the PM frequency. What is the best way to write a formula for this?
-
How to put a date and time together in a column?
Hello, i am fairly new to smartsheet and Formulas, i was using a Created date column in Vlookup but looks like that column is in TEXT and is not being read as a Date and time.(maybe i am wrong). i have managed to use a couple of formulas to bring out the date and time in 2 separate columns, i want to put them together now…
-
IF and AND Question
I think this is fairly simple but I cannot seem to get it worked out. I have a Finding Status column. If "Closed", then populate a checkbox column with an unfilled flag. If the Finding Status column is "Open," then look to see if the deadline column is less than Today(), if yes, then the checkbox should be true (solid). I…
-
RYG Parent Changes with Daughter Scheduled Health Change
Dear Smartsheet Team I am trying to get Parent [Scheduled Health to reflect 'worst color' from daughter lines. The logic: if any one of Line 3 or Line 16 or Line 20 (of the [Scheduled Health] Column is Red then Line 1 is Red If any one of line 3, 16, or 20 is yellow then line 1 is yellow if all line 3, 16, 20 green then…
-
Join ancestors EXCEPT the top level
Hi Smartsheeters, I would like to group my milestone report by workstream, so I added a "join ancestors" column. I also have a "count ancestors" column that can be used. My current report is a nightmare when sharing, as the top ancestor is the project name, and I can't control how massive (and not wrapped) the group header…
-
COUNTIF formula calculating a checkbox column incorrectly
Hi there There is most likely an easy fix to my issue, but I just can't crack it myself: I have two COUNTIFS formulas to calculate how many checkboxes are ticked and how many are unticked. The COUNTIFS formula for the ticked checkboxes is working 100% - there are 5 check boxes ticked, which I counted manually to confirm…
-
INDEX, COLLECT, CONTAINS Formula help
Hi! I have a formula that is working the way I want... =IFERROR(INDEX(COLLECT({LFH Date}, {LFH Date}, @cell > TODAY(), {LFH Analyst}, Name@row), 1), "") But when I add another condition, I get #INCORRECT ARGUMENT SET. This is the formula: =IFERROR(INDEX(COLLECT({LFH Date}, {LFH Date}, @cell > TODAY(), {LFH Analyst},…
-
Return a value from the previous month in a reference sheet
Hello, I'm trying to bring in the average score from the previous month in a reference sheet, it's returning an error. Here's the formula in text: =IFERROR(INDEX(COLLECT({Archived ATL Audit Scores - Combined Average}, {Archived ATL Audit Scores - Combined Area}, Area@row, {Archived ATL Audit Scores - Combined Concourse},…
-
Is it possible to filter out submissions from a form based on if the email field?
Hello, I am learning smartsheets and am unable to find a way to do this. We have a form for one of our sheets so people can submit requests to my team. However, we want the requests to only come from our organization. The form obviously has an option to only allow submissions if they have a smartsheets login but that…
-
Average with multiple criteria
I am trying to get the average of a column named "Average working Days" while the "STATUS" = "On Hold", "In Progress" and "Not Started" and when I tried it, it gives me #Divide by zero. =AVG(COLLECT({Request Tracker with Form Range - Average}, {Request Tracker Range 3}, Category@row, {Request Tracker Range 2 - Status},…