-
Index Match Returning Unexpected Value
I feel like I've posted 1000 Index Match questions this year! I have a formula (below) that is returning and individual who has the same Date Assigned field but not that same MLL ID. The MLL ID is supposed to be the thing that stops the date range from looking at all the cells. I'm trying to get Ciara as a returned value…
-
Creating A Report of Missing Submissions
Hi Community! I have a form deployed to community partners. These partners need to submit a report 1x per month. Of course, some do not submit the monthly report. Is there a way for me to create a report of missing submissions? I have a reference of all partners who should be submitting a report and the sheet from the form…
-
Using IF/THEN for my formula
Hello, I recently received advice on here to develop a formula that lists out all projects that individuals are billing to via cross sheet formulas. The below formula would go into the Projects column in the Enterprise Rollup. =JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ") This formula works, however, it is…
-
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…
