-
VLOOKUP on column with letters and numbers results in #NOMATCH
I have a simple VLOOKUP to pull back the related column from a 2 column table. It consists of either letters only (WORD) or numbers only (123) for each entry but entries span both types. When the function is in place I receive a #NOMATCH error for all the letters only entries. I've tried adding a helper column that refers…
-
IF/AND/OR Functions
Good Morning, I am looking to get some help setting up a formula based on a few conditions. The first condition is IF column BAG COUNT has a value = 0 AND column Response Form Received = Y then if the statement is true it should input YES into All documents if False NO. The second condition is if column BAG COUNT has a…
-
Record a Date not working
I'm using a smartsheet to record requests that need approval. I want the "Requested On" date to be automatically populated when someone adds a new request to the sheet (i.e. fills in a new row with info such as Order Number). I set up a workflow (see below) but when I go to my sheet and add information into the Order…
-
SUMIF error
I'm stumped on another formula. I receive an "incorrect argument set" Total [Dave Delivered] if the month is January =SUMIF([Actual Date Completed]3:[Actual Date Completed]46, MONTH(@cell) = 1, [Dave Delivered]3:[Dave Delivered]40) Thank you
-
Calculating average over month
Hello, I am trying to calculate average of days spent per assessment over a month, but my formula is returning a zero even though the data does not support this. Here is the formula I am using: =AVERAGEIF({Temperature Assessment Site Days}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)), {Temperature Assessment…
-
Max Collect formula help
Hello I can't seem to troubleshoot this formula. Return the latest date [Planned Date Completed] associated with "1" [SOW Billing sequence]. Any idea why it's returning 0? Thank you! Lauren
-
What formula do I use for Grading?
I have a sheet used for grading our product. There is a final score for each product out of 36. I would like to use a formula that uses the final score to give the product a "Grade". 0-12 = C, 13-23 = B, 24-29 = A, 30-36 = A+ How do I set up a formula to kick out a letter grade in a cell depending on a range of numbers in…
-
RYG: Green with Conditions
Smartsheet Community: I am trying to Add a Condition to my Green Formula: =IF([Schedule Variance]@row >= 0, "Green", IF([Schedule Variance]@row <= -7, "Red", "Yellow")) I would Like it to Be Green Only if Schedule Variance is Greater Than or Equal to 0 AND % Complete (Actual) is Greater Than 0, Otherwise Blue in this…
-
Too many Formula Cell references
Hello, I received this warning in my Smartsheet and am not sure why? I have about 36 columns and ~2400 rows, 11 columns are formula columns. Do I have too many formulas? I have 6 cross sheet references. Appreciate any help.
-
#INVALID DATA TYPE error when IFs are combined
Hi all, I've got this formula here that basically enters name of the month in the cell based on date: =IF(MONTH([Date of Defense]@row) = 1, "a. JANUARY", IF(MONTH([Date of Defense]@row) = 2, "b. FEBRUARY", IF(MONTH([Date of Defense]@row) = 3, "c. MARCH", IF(MONTH([Date of Defense]@row) = 4, "d. APRIL", IF(MONTH([Date of…