-
Index/Match across 3 sheets resulting in #invalid value
Hi all, I have lots of experience with Index/Match in Excel and Smartsheet and this one has stumped me and I can't figure out why. Here's the scoop: I have 1 sheet (Sheet 1) where I import an xlsx document with a bunch of entries (with a PO Number, among other things); Sheet 1 would be my INDEX range for each of the…
-
Column to indicate whether predecessor tasks are complete
I'm looking for a way to add a set of formulas on a sheet that can work together to produce a checkbox showing whether a task's predecessors have been completed. Based on another post I saw, I have gotten to the point of having a column that identifies a row's successors, but can't figure out the formula for the next…
-
Getting #UNPARSEABLE and Need Help
Hello Community, I am struggling to figure out why I keep getting #UNPARSEABLE on the below formula. I am trying to add an IF statement for when [CIT PPO #]@row is blank, the result is "Non Compliant". Any help is appreciated! Broken formula: =IFERROR(IF([Job Complete]@row = "", "Non Compliant", IF([CIT PPO #]@row "", "Non…
-
10 digit fixed numeric field
Hi, I have few fields where I want to add a validation/control user to force doing the correct entry - e.g. PO# shouldn't be more or less than 10 digits. Is it possible? They do these entries via Form. I see their are new validations numeric/text etc...they don't help what I need. Also, Can we trigger any flash alert for…
-
Is it possible to hide dates in a project plan and display week 1, week 2, etc.?
I'm setting up a project where the start date is still to be determined. I have been asked to provide a column that has the week a certain activity needs to occur in. For example, instead of kicking off the project on 25/09/2023 the Grid View of the project plan should have a column in it that shows the project kicking off…
-
Formula Assistance
I'm trying to create a automated system in Smartsheet's that takes criteria from a form submission (Unit, Start Date, End Date, Day of the Week and Shift time) to cross check another Smartsheet that has the full calendar year by week and if all criteria from the submission match on the calendar sheet for the…
-
Summary Field that Averages Field Entries that are Less than Zero
I have a calculated field for the time over or under a deadline date. I want to create a summary field that summarizes the number of days overdue for projects completed after their deadlines. Right now I'm trying =averageif([field name]:[field name],<0,[field name]:[field name]) I get an "Invalid Operation" error when I do…
-
Column Formula + Reports
Curious why a column that has a formula applied cant be changed when it's pulled into a report - from the report? Also wondering if there is a way that is quick to remove the formula from an entire column? I've switched one back to 'cell formula' to allow for edit from the report but, would like to avoid running down 600…
-
Question on Function Syntax using IF, AND, and OR
I am trying to get an At Risk check box to return a red flag if there is a date in End Date and that date is earlier than the curent date and the Status cell shows one of three statuses. Can't quite get the formula logic. Any suggestions would be wonderful. Thanks! Desired results: End Date is blank = no flag End Date is…
-
Nested If Contains Function Returning #No Match
Having and issue with a nested =IF(CONTAINS( function returning a result of #NO MATCH. Formulas work independently of each other just not nested. I also confirmed the "search value" within the VLOOKUP is on the lookup table. What am I missing? =IF(CONTAINS("ABC", [DC Location]:[DC Location]), VLOOKUP([Lane Concat w/…
