-
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/…
-
IF/AND/OR Statements Help To Check A Box When Criteria Met
Hi, I am trying to write a formula with multiple IF statements with OR and AND to check the appropriate box: I have this so far and get #INCORRECT ARGUMENT: =IF(AND(NOT(ISBLANK([Term Date]@row))), OR(Program@row = "ABC", [Shared Programs]@row, OR(@cell = "ABC / XYZ", @cell = "XYZ / ABC")), 1, 0) Thanks for any help…
-
Issue combining data of 2 fields
I need to combine data of two fields. 1 field is a dropdown and the other open text. The first option to combine them didn't work: =TEXT([Content Subset]@row, "General") & [Playlist Brand]@row The second option was to convert the 'Content Subset' to text and then combine, but that again didn't work: =TEXT([Content…
-
COUNTIF based on a Sheet Summary Cell w/Formula?
Hello! I am trying to create rolling metrics of how many entries we have in the current Fiscal Year, Quarter, and past Fiscal Year. How would I reference a Sheet Summary cell's value? For ex, I want Total Orders of Fiscal Year to show me everything in FY23. My Total Orders of Fiscal Year cell has the formula-…
-
My Date Helper Row is Showing the Wrong Centure 1923 Instead of 2023
Hi All, I created a sheet where my users will select from a dropdown of available dates. This column is set to Drop-Down so they only can select the dates I offer. (Column Name = Selected Date) I have a helper column that is set to "Date" and has a formula to turn the text data into a Smartsheet date. (Column Name = Date…
-
Apply formula to full column
As lines are constantly added in a workinglist, I want to ensure any new lines will always have a column with a formula filled in. The formula will check certain data in fields on that line to show a specific status/value. I read that i should be able to 'insert' a formula on a full column, but can't find it. I'm only able…
-
Excel Text Formula
Hi, I am trying to use an Excel formula to extract the full month from the date. I am using the excel formula: text([Due Date]@row,"mmmm") and I am getting an error. Is there a similar smartsheet formula ? Thank you !
-
Sum of Values Between Two Dates
Hi All! I am trying to write a formula that add up the total "crew size" required between two dates. I've created a metric sheet to hold the formula. See screenshot of parent sheet below. Formula: =SUMIFS({Shop Overall Schedule Range 1}, {Shop Overall Schedule Range 2}, >=DATE(2023, 9, 1), {Shop Overall Schedule Range 3},…
-
INDEX (COLLECT) Formula returning #INVALID VALUE Error
I am trying to use INDEX COLLECT to pull data out of a specific column from another sheet based one three criteria. My formula is: =INDEX(COLLECT({Site Visit Intake - DOM Review %}, {Site Intake FDC-FW}, FW@row, {Site Visit Intake - DC}, DC@row, {Site Visit Intake - Metric}, [Metric Being Evaluated]@row),1) I keep getting…
-
Combined Multiple IF CONTAINS Functions
Hi, I have the following formula =IF(CONTAINS([Column A]@row, {Sheet A Range 1}), "Sheet A", IF(CONTAINS([Column A]@row, {Sheet B Range 1}), "Sheet B") Now I want to add if both sheets contain [Column A]@row, result: "Sheets A and B", How do I add this last part in addition to my current formula? Any help is appreciated,…