-
How to combine these?
Need the formula to say: If Completed - Green If Canceled - Gray If In Progress - Yellow If Not Started - Red If On Hold - Yellow If In Progress, Not Started, or On Hold and and today is greater than End@row - Red Here is what I have.... Works fine. Now need to add in the last bullet and I'm stumped. =IF(Status@row =…
-
Is it possible to URL-Query an embedded form?
I am wondering if it is possible to prefill an embedded form on a dashboard by referencing the form in the dashboard URL?
-
Automatically change Parent status to display Children status; still be able to change Child value
Hello, I have this formula =IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Completed", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") =…
-
Index Collect Formula for Dates resulting in Invalid Value Error
Good afternoon, I am trying to use an Index Collect formula to collect dates that have dependencies. Here is the formula I'm using: =INDEX(COLLECT({Portfolio Summary | Actual Start}, {Portfolio Summary | Project Number}, [Project Number]@row), 1) + "" I can confirm that the formula cell is a date column, as well as the…
-
If all Child rows = "Complete" change Parent to "Complete" and send to new sheet
Hello! I have an automation that sends "Complete" Parent columns to a new sheet (my "Closed Orders" sheet). I also am setting up a formula for when all Children status' are set to "Complete" it automatically switches the Parent row to "Complete". What I am wanting to do is send ALL rows including Parent and its Children in…
-
Change Date column if new row is added after hours
Hello - We have a simple ticket system and if a ticket request from a form comes in after 4:00pm, we don't start the ticket until the next day. Case: Form Entry happens at 2:30pm on 07/27/23. Start Date column should read 07/27/23. Form Entry happens at 4:30pm on 07/27/23. Start Date column should read 07/28/23. So we want…
-
countif events from last year
I am trying to count how many event happened last year (2022) as opposed to this year with a countifs, I can't seem to find a countif(ref, year(2022)) or countif(ref,YEAR(@cell)=YEAR[fiscal year]2) with [fiscal year]2=1/1/2022 to be working. Since the countifs is a number with multiple criteria and I'm also looking in a…
-
Why is my IF(AND(HAS function only checking part of the AND statement?
I am using a checkbox column to return 1 if a row on this sheet matches the Student Name and Location Name on a row of another sheet. Formula: =IF(AND(HAS({Extern}, [Student Name]@row), HAS({Location_No_ID}, [Assigned Clinic]@row)), 1, 0) The result is showing a checkbox when only part of the statement is true. I need both…
-
Need to remove leading zeros
I have a column that represents 'Store #'. Store #'s are 4 digits, i.e., 0001 or 0010 or 0100 or 1000 This becomes a bit of an issue when sorting the column. I am creating a helper row for purposes of sorting and want to remove the LEADING zeros, however, am having trouble figuring out how to accomplish this. Thoughts?
-
Fill in a cell based on another cell's value?
Hello, I'm tracking participation of a program on my sheet as a "participation %" column. There is another column called "participation status" to make it easy for the managers to know if their employee is on track with participation or not. I'm trying to set up the participation status column so that if the participation…