-
Comparing Dates
Hi, I have a Planned Date and Actual Date and Final Date column. I need formula where Final Date column, has Actual Date, but when Actual Date is blank Final Date should have Planned Date. Planned Date | Actual Date | Any help is much appreciated! Thank you!
-
Total Payment monthly breakdown based on Start and End Date Columns
Hi Everyone, I need some support with a formula, if anyone can help me 😁 I have the below sheet where I have created costing for resource use between a Start and End Date Column supported by a total day column count between the two dates and use a day rate column to multiply the total column. What I would like now to do,…
-
Formula to count how many interviews are within the last 7 days and are this week?
I am try to find the right formula to count how many interviews are within the last 7 days but also count anything happening in the next 7 days. The formula I used for the last 7 days is: =COUNTIFS([Interview Date]:[Interview Date], @cell <= TODAY(), [Interview Date]:[Interview Date], @cell > TODAY(-7))
-
Array formulas for project completion in Smartsheet
Hello, I have a project plan in Smartsheet with 2 columns: % Complete Relative weight of completion This cells are used in parent rows (the phases of the project) and are used to indicate the completion of a phase but also the relative amount of effort that each phase has (as an average is not accurate enough) I would like…
-
result with index/match but not index/collect
Hi, I have a formula with multiple conditions, so I used index/collect to pull the data. However, that didn't pull the data. Basically, if the ID# in sheet A matches sheet B and the details column in sheet A is not blank, it should pull the details in sheet A. =IFERROR(INDEX(COLLECT({details}, {ID#}, [ID#]@row, {details},…
-
How to check if children cells are blank?
When the following formula is applied =IF(COUNTIF(CHILDREN(), OR(@cell = "Yes", @cell = "N/A")) = COUNT(CHILDREN()), "Yes", "No") it results false positive values since blank children cells are counted. There is ISBLANK() function. How to use it to apply the above formula only if all children cells are not blank?
-
Array and Index Training Records to pull who needs to complete something within a date range
I manage training records and am creating a dashboard to manage who needs to complete a training. I have a master sheet with the employee name, and a reference column to them completing the training and when it is due I then have a calculation sheet to count how many people fall within that date range =COUNTIFS({HiPot…
-
Convert this formula to a column formula
This formula is working at the cell level but I would like to convert it to a Column formula so it automatically applies to new entries added to the sheet from a form. =Weight2 - $Weight$1
-
True count of Children(), not just non blanks
Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…
-
Multiple cells locked to single formula in same column
I'm relatively new to Smartsheet and use it as a tertiary tool, so apologies if this is a 'basic' question. What I want is to tweak a formula on one row of a column and have it apply all other rows that reference that cell in the same column so I don't have to copy the tweaked @row + Parent, Children, or Ancestors…
