-
Is a daily entry missing?
We use a form that allows each of our 120 stores' managers to input some data at the end of each day. Is there a way for me to see which stores missed a day? Each form entry requires the store name (drop down), and I have another sheet that has every store listed in it. Trying to think of a way to have a…
-
RAG status help
I am trying to right a formula to give a RAG status based on 6 columns of check boxes. I want Green for all boxes ticked, Yellow for some, and Red for none. This is the formula I have but it comes up as Unparseable =IF(COUNTIFS([PTC CRBA]@row:[PTC Aud]@row, 1) = 6, "Green", IF(COUNTIFS([PTC CRBA]@row:[PTC Aud]@row, 1) < 6,…
-
How do you reference 'Created' column date of a PARENT and add one calendar year?
Hi all, I am trying to do the following: For a given cell, I would like to reference its Parent's Created date and return a date that is one year in the future. I think I'm just struggling with finding the correct syntax.
-
Using a profile matrix to drive automation on specific dates
I have a profile matrix that has a set of dates across the top (columns), row 1 and then a list of product types (Student, Teacher, Classroom) for each record, starting at row 2. On a separate sheet I have a list of products that associate to the various product types, i.e. Student Workbooks (Student), Instructional Manual…
-
SUMIFS Formula
I am trying to create a SUMIFS formula referencing another sheet: =SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, "Trainee Solicitor") The column I am trying to add is a formula so, I have tried VALUE in front of the above…
-
Can Formulas Reference Reports?
I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person. When I go to do that and the formula asks me to reference a sheet, I am only…
-
Count if NOT BLANK
Hi. I am trying to write a formula with multiple criteria to for my metrics sheets; however, I'm not sure how to add this one count if not blank. This is my current formula: =IFERROR(COUNTIFS({OHCA Form - Agency}, $Agency@row, {OHCA Form - Review Complete}, "Medical Review Complete", {2020 OHCA Form - Etiology},…
-
Automatic indentation
Hi, I am using Data Shuttle to bring data from an external source into my SmartSheet. I want to automatically indent based on the Unique ID as shown in the below example. The Unique ID with the lowest suffix should be the parent row and subsequent ones should be children. Is this possible to achieve with a formula or some…
-
Countif with Min and Max Date Range
So the excel formula I'm trying to replicate is below. Basically, I'm trying to count every cell that has a date between Jan. 1, 2021 and Jan. 31, 2021. The outcome should only be one number. =COUNTIFS(Sheet1!N:N,">=1/1/2021",Sheet1!N:N,"<=1/31/2021") But I've tried so many things. I can't figure out what try next. I've…
-
IF AND formula using ANCESTOR
Hello! I am trying to automatically check a checkbox to a cell based on these conditions: The task's parent is "Releases" (task column is named Category) Status = green (RYGB symbol). Here's where I am and it's stating it is unparsable: =IF(AND([Status]@row ="green", PARENT([Category]@row="Releases"),1,0)) Any help is…