-
Delimit Date/time in a match/index function
Hi all, I'm using a match/index function currently to pull over data from another sheet. Currently I'm using this: =IFERROR(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH(claimNum@row, {claim num}, 0)), "") The sheet I'm referencing in the function has time and date in the same column, so the above formula…
-
How to subtract percentages?
I created a new column % Not complete and am trying to add a formula to have =100%-[%Complete]@row. Answer #UNPARSEABLE I have also tried =100-%Complete]@row. Answer: This will populate an answer, however I cannot adjust the formatting from % to number Example: %Complete = 13% Correct answer should display 87% Second…
-
AUTO TASK ID HAS A FLAW - URGENT NEED TO FIX
The Auto Task ID column is auto-generated by Smartsheets and is supposed to be a UNIQUE identifier. Shockingly, it generated two of the same IDs, this is a SMARTSHEET bug. (Several formulas rely on this ID to be a unique identifier). Perhaps somehow two users saved the sheet simultaneously or something to create this bug,…
-
Referencing value in previous row to assign a contact
On a Requests Sheet, I have a column for Assigned Supervisor. I want to write a column formula that will add a contact based on the previous row's assignment. I'd like to rotate through a list of supervisor contacts each time a form is submitted. I created a separate Assignment sheet that has a column of the Supervisor and…
-
Adding Overall % and Date Questions
I am using the Project Manager Office Template from Smartsheet. I would like to have the very top row (it is almost like a summary row), calculate the overall % complete of the project and adjust the start and end dates as the schedule changes/ progresses. I tried adding a formula to both sections but it would not actually…
-
SUMIFS with CONTAINS
Hello, I want to write a formula that SUMS charges ("Ext Price" column) from all item descriptions that contain a word ("CF") from an accounting "Activity Description" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, "CF", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…
-
Using SUMIFS Formula
Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, "AMR", {leadership}, "Truttmann")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!
-
Formula that combine names that falls into the selected dates
Hi, I have a sheet on which employee will submit their PTO request. The column "Team PTO" shows who else is having PTO during their requested dates within their department. Here's the formula: =JOIN(DISTINCT(COLLECT(Employee:Employee, [PTO Start Date]:[PTO Start Date], >=[PTO Start Date]@row, [PTO End Date]:[PTO End Date],…
-
How to identify if Contact -OR- Text is in a Contact column?
Hi everyone - We have starter task templates that we use when we initiate certain types of work. The templates have a Contact List column that is initially populated with ROLES (e.g. project manager, designer, tester, etc). The ROLES are then populated by the project team with the actual CONTACT names, based on the team…