-
Check Another Sheet for Value across Multiple Columns
In Sheet 2, I am looking to search Sheet 1 for an instance of a value, that could be in one of four columns. Sheet 1 COLUMN A | COLUMN B | COLUMN C | COLUMN D | COLUMN E | ABS 123 | March | 26444 | | 43454 | ABS 456 | June | 87834 | 45003 | | ABS 789 | August | | | | ABS 000 | October | | | 36543 | There are four order…
-
Count the number of times a text value appears in a single cell (or alternatively, split the cell)
Hi Smartsheets community, I have a column of data with cells that contain text values that I need to count. For example, the cell in one of these columns may contain something like: "ABC123 - TEXT TEXT TEXT ABC008 - TEXT TEXT TEXT" The two lines of "ABC" text are separated by a carriage return. I'm trying to count how many…
-
Linking Green Health Indicator with Not Started Status
Used this ask a question feature for the first time last week and it has been a lifesaver. Here goes another one... How do I update my formula's to show the following. Not sure if I need to update both the health and status, or just the health. Show status as 'in progress' if the start date is greater than today's date…
-
Can someone assist with looking at children rows vs selecting exact cells?
Hello! Each time I change the sorting of this sheet, my formulas are changed. The formula is technically correct, the issue is that I am referencing exact cells, so when sorting it throws it off. =SUMIF([Open By Year]38:[Open By Year]43, @cell < TODAY(), [Total Units Signed For]38:[Total Units Signed For]43) - [# Open]@row…
-
How to have Percent Complete for two columns?
Hello, I'm using the “New Hire Onboarding Template” that my team has expanded on. We added another checkbox column, and I'm hoping to have it be included in the % Complete section as well. Below you'll see the current formula that “came with” the template. How could I add the “Delivered” column to be included in this? I…
-
Formula troubles.
I am trying to rewrite a formula and I need some help. It is to update a due date for recertification. The midyear criterion makes it more complex. Here are the parameters that reference other columns: the person's original certification date, and their 4 year recertification cycle. I am trying to get a column to autofill…
-
Need help with CountIF formula referencing two columns on another sheet with an OR statement
Current attempt: =COUNTIFS({Sheet Name - Standard}, CONTAINS("UM", @cell), {Sheet Name - Annual}, OR("yes", "add")) The gist is, another sheet, Sheet Name, has a column, Standard, and a column Annual, and for every Standard that is UM, we want to count if the Annual column contains "Yes" or "Add". There is a third option…
-
How to get the Delta between Estimated Duration and Actual Duration
Good Morning, We are trying to move our deployment plan for technical project implementations into Smartsheet. What I'm struggling with is getting the delta between estimated duration and actual duration to account for working days only. My start date/time, end date/time and actual finish date/time are all manual input.…
-
Broken Vlookup (No Match)
My vlookup is returning no match even though there is a match avaliable. But the reference sheet has an avaliable rate:
-
Can you extract text from a cell to use within a formula of another cell?
I'm trying to dynamically update the lookup_table value in my VLOOKUP formula based on a selection made in another cell. Thank you, Matt
