-
Return a value based on the date and section
Hi, I'm trying to create a sheet for a department production report, This department has 10 sections, each section do different type job but the project is same, i'm keeping all section work in a mastersheet so that i can filter however i want to generate a report. the Problem i'm facing is the closing stock should be…
-
Sum data and not change data after certain date.
I am trying to sum data as of XX/XX/XXXX of each week. I want this data then to go into a cell and not be changed the next week. For example, on 1/15/24, a sum formula was used to calculate the number hours remaining was 3891. On 1/22/24, the sum formula was used to calculate the number of hours remaining as 3201. Is there…
-
IF with INDEX / MATCH
Hi. I use INDEX/MATCH regularly, but want to add an "IF" statement and am getting #UNPARSEABLE, and would like your help. I'd like to pull the "F Name" from Sheet 2 into Sheet 1, when the "Info Level" in Sheet 2 = "Primary". This formula returns #UNPARSEABLE: =IF({Sheet 2 Info Level Range} = "Primary"), INDEX({Sheet 2 F…
-
Creating a multi-sheet formula to match a contact list that references a drop-down list
Hello to you, Smartsheet community! I am in need of assistance with formulas. I'm so lost in the syntax that I don't know right from wrong, up from down, HAS or COLLECT 😵. I do not identify as a power user with formulas — wizards are my dearest friend — so this push into the advanced features has been a journey. Is it…
-
IF AND OR Formula *almost* works correctly but one part!
Hiya folks- all arguments work correctly except turning Health the color Yellow when Recommended Due Date is within 3 days of current date and status is not Complete. Here is what I need to accomplish: IF [Status] is"Complete" or "Cancelled/NA" then [Health] ="Blue" IF [Recommended DueDate] <= TODAY and [status] <>…
-
COUNTIFS Error
I'm receiving a #NO MATCH error with a simple COUNTIFS formula. I'm trying to get a count of the number of times "Routine" occurs in the column, "Workover Type (Routine, Non-Routine)" (text/number column, with a formula), when the "Status" is "SI - Unassigned to WO Rig" (dropdown list column type). Any help is appreciated!
-
Multiple Sequences Need to Be Complete/NA to Provide a Complete Value
Writing formulas is not a strong suite of mine - I've tried a few routes (IF OR AND) but I'm either running into invalid formulas or formulas that are not netting the right results. I am trying to write a formula where I'm looking for sequence values to be either Complete or N/A and based on those sequences being one of…
-
IF statement based on cells in same row
I have a sheet where every row requires two approvals before it's fully approved. What would the formula be to make this work? For example, approval column 1 is named "initial approval" and approval column 2 is named "COHS approval". Both cells are dropdowns with the options pending, approved, or denied. I want the first…
-
formula
I need a formula that reflects the remaining due if not paid in full isnt checked off otherwise reflect it as a $0.00
-
Using NOT with JOIN/COLLECT
Hello Everyone! I am creating a formula that returns the ID if the item type is "Book" and the status is NOT "Sold". My current formula I was trying is: =JOIN(COLLECT({ID}, {Item}, "Book", NOT({Status}= "Sold"), CHAR(10)) What is the correct way to return the ID for an item that is "Book" and the status is NOT "Sold". (Or…
