-
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…
-
Creating Formulas for Status, and Task Health to send Email Notifications
Hello, Everyone I need help with my syntax on some formulas and sending email notifications. • Status ○ If the %Complete is 0%, Status = Not Started ○ If the %Complete is between 1%-99%, Status = In Progress ○ If the %Complete is 100% = Complete § Questions: What if the task is no longer needed? In MS Project, we would…
-
Please help me convert an Excel formula into a Smartsheet formula
The following formula works in Excel and I'd like to recreate it in Smartsheet: =if(C2+1, D6) In Smartsheet, my column C is called Session, and column D is called Training Date. The Session column includes numbers only. Please let me know if you need additional info, thank you!
-
Help with formula
I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…
