-
Formulas for COUNTIF on Fiscal Year
Hi Smarties, I would like to count the number of applicants based on the Stage as below. However, I would like to only count if they fall under this fiscal year (July 2021 to June 2022). My current formula is split to three rows (Taking Successful (R5) as an example) This Year : =COUNTIFS({Created Date}, >TODAY(-365),…
-
What is wrong with my automated colour column formula?
I am looking to automate the colour “status” for my rows based on the health of the action - a calculation based on the ‘% Complete’ and the ‘Due Date’. The goal is to demonstrate a contextual health status. For example, even if an action is only 10%, if the due date is still 6+ months away, the action is still Green due…
-
Scoring Formulas
I'm looking to convert a process to smartsheet, but am trying to determine if there is a formula to make the concept work. Currently I have a form which asks a series of questions - each question has a single select option of "yes (description)" "no (description)" and "n/a". For each question, there is a score associated…
-
Bridge vs. Zapier vs. UiPath vs. ???
I'd love to hear feedback and use cases for anyone using Bridge, UiPath, Zapier, or another external system in tandem with Smartsheet (yes, I know Bridge is a Smartsheet product). I've used Zapier in the past, but only for simple tasks. The company I recently joined is using a lot of Power Automate and Flows and it feels…
-
Help with VLOOKUP
I am trying to pull data from one sheet into a summary sheet. Basically I am looking to pull if a given site met a target on a given day -- an admin is entering in the number of employees reporting for work on a day for each of 12 sites into a Smartsheet (called KU MR Attendance), and I need to see if they are meeting the…
-
I'm Trying to flag if a colum says RISK but it comes up as #UMPARSEABLE
Help please =if(Status@row=RISK, 1,0) Is not working
-
Check a Box, Reference Another Sheet, Days from today
Hello, I am working with 2 sheets. 1 sheet is providing a summary of the other sheet. I need assistance with creating the formula. On the summary sheet, I want to check a box if there is a row listed on the other sheet with the same city and has a scheduled date of 6 months from today. Formula: =IF({Site}, =Site@row,…
-
Looking for help with "Contains"
I am looking for some help with the Contains function. I currently have the following formula which is working fine. =COUNTIFS({2021 Archive Range - RFI Reason}, "Census", {2021 Archive Range Assigned To}, "JP Pedicino") I need to update it to search for CONTAINS "Census" in the 2021 Archive Range - RFI Reason range and…
-
Setting "Date Completed" column date based on status change
Hi! I'm trying to set up a formula for my Date Completed column that pre-populates the date in that field to the date where the Status on the same line = "Complete". Right now I'm using this formula: =IF(OR(Status125 = "Complete"), TODAY(), "") One problem is that it doesn't add the formula for newly added line items. Also…
-
vlookup error
I am trying to write my first vlookup formula in Smartsheet and running into an error I can't resolve. This is the sheet that I am writing the formula in: This is the formula I've written: And this is the lookup_table am referencing: The Detail Status that is my search_value is in the first column of my lookup_table. My…