-
Why does my INDEX(MATCH()) column formula return #NO MATCH for some values?
I have a simple formula (below) that references another sheet matching the value @row on the destination sheet. This is a column formula in the [P6Description] column. =INDEX({Source Sheet - Description}, MATCH([P6Number]@row, {Source Sheet - Project Number})) Why am I getting #NO MATCH for the project numbers starting…
-
Fillable Drop Down or similar?
Hi There! I am looking to create a smartsheet where staff can input multiple names without having to do multiple forms back to back. For example the question would be: who took the class? and then the staff would input names that would populate onto different lines int he database. Can I do that with drop down? Or contact?…
-
Formula IF OR CONTAINS - Change State to State Abbreviation
I have a State Column and a State Abbreviation Column. The State column is a dropdown column that is filled out from a Form. I have a Formula in the State Abbreviation Column that looks at the State Column and then populates the appropriate State Abbreviation. Here is my issue for West Virgina and Arkansas it populates…
-
Change Status Based on Previous Row
I am trying to create a column formula that will change the status of a row to "Ready for Work" when status in the row above it changes to "Complete" I tried to follow the information that was provided this thread, but I keep getting #UNPARSEABLE: I already have an auto-number column (Job # - font is just in white based on…
-
What formula will return one of three date fields based on set critera?
Okay, so here's another one for the community We have a tracker with three date fields. The PM Received date #1 should take priority and be returned if not blank, otherwise, we want one of the other two dates, whichever is not blank. (and only one of #2 & #3 will be not blank) PM Received Intake Date: The actual date…
-
I have multiple nested formulas but one doesn't return anything... Help
Hi All, I'm trying to automate the status of a project plan. We have 4 statuses, Not started, In Progress, Behind, and On hold / cancelled. Here's what I want to happen: If [% Complete] is blank or = 0, and [Start] is in the future, then "Not started". If [% Complete] is >0 and <1, and [Finish] is in the future, the "In…
-
Count children rows where multiple criteria is met
I am working on reporting of a tracker to return a "1" in a column for only children rows, where specific criteria are met in other columns in the same sheet. Below is what the AI formula maker returned, but I am getting an error back. Any ideas? =IF(AND(COUNTIF(CHILDREN([BB Label Approval Status]@row), "complete"),…
-
AND-OR-COUNTIF Question
Hi, I started to build a formula and it doesn't return the required values. I have a sheet for distribution board testing. There are 25 yellow checkbox columns and 24 green checkbox columns. I have a column with 4 colour symbol (L1-Quad), and a checkbox column (All DB Tested) where the formula needs to return the value. My…
-
Formula produces #CIRCULAR REFERENCE
I'd like to compare the dates in two rows, i.e. Ende (Plan) and Ende (nach Verschiebung). If Ende (Plan) is older than Ende (nach Verschiebung) the date should change to red colour (or somehow be highlighted). The AI tool in Smartsheet proposed the following formula =IF([Ende (Plan)]@row < [Ende (nach Verschiebung)]@row,…
-
Form: Require "Need order by" field to be AFTER today.
We have a form that has a DATE field called "Need Order By" so that the user can tell us the date the the order must be fulfilled by. Can you guess what date users MOST OFTEN use? Yeah, you got it! a date in the PAST. We are very anxious to find a way to force the user to input a date that is in the FUTURE. Can anyone tell…
