-
Any ideas why a formula wouldn't work on all rows?
I have a formula that is working on at least one row, but not all rows, and I have the necessary data in all rows for it to work, so I am boggled as to why it isn't working on all rows : =IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red",…
-
WEEKNUMBER formula: fix issue when 31th december is in mid week
Good afternoon from Spain. I faced an issue with WEEKNUMBER formula. 30th december is monday, 31th december is tuesday. If we calculate their weeknumber, the answer is '1'. This behavior is incorrect. 30th and 31th december should be = 53 (week number). I wanna ask to fix this. When one of the last days of december are is…
-
COUNT MATCHING VALUES FROM TWO DIFFERENT COLUMNS ON A SEPERATE SHEET
Hello, What I first thought would be an easy formula has turned out to be quite a pain. I am trying to count the number of rows where one column matches the other. My formula returns #INVALID OPERATION. =COUNTIFS({Agent}, @cell = Name@row, {Offer Amount}, {Secured Amount}) The thing that confuses me is, even when i go to…
-
Help with TIME() Formula
Hello, I am testing out the TIME() function and working on a formula to determine the amount of hours and minutes elapsed between a Start and Finish columns. I thought I had it figured out, however, I noticed that for my row that has a Start time of 3 pm and a Finish time of 5 pm, it was showing a time difference of "1…
-
Autofill child row cells with information from parent rows
Hi! I am trying to get my Smartsheet to autofill information in some cells based on the parent row. The sheet is mainly viewed in card view so when my teammates add subtasks they don't fill in basic info like the team working on the project since that is already in the parent task card. I tried a basic =parent but that…
-
VLOOK UP or INDEX/MATCH to pick values for duplicated unique ID based on certain criteria
Hi , I have a main tracker that tracks projects progress, and a separate field tracker that has our field team submissions through a smartsheet form. The field workers submit completed date for projects using a unique ID. In some cases they may submit future projects with the anticipated date of completion. In the form…
-
Restrict Row Cells to Assigned Person
I have a sheet that assign task to different people with checkboxes in columns for them to mark when done. Is there a way to restrict the checkbox access to only the person assigned on that row? Thanks!
-
Formula to automatically calculate modified start date
I have created a workback schedule along with dependencies. One of the requirements is once a due date is changed a new start date is autopopulated in the column. For this purpose, I created 2 helper columns modified due date and modified start date. Can anyone please tell me if I am on the right track? Thanks
-
Moving rows automation with keeping layout of parent/children
I have been able to set up automation on my sheet to move rows to another sheet. It does add rows to the sheet but, it puts them at the bottom. I am looking to move the row in the particular parent-child grouping it exists in on the original sheet to the “closed/archived” sheet. I do have the sheets set up identical to one…
-
Baselines & Variance If Formula
I have created a new Health column and I'm trying to use the Variance number from the baseline to reflect the following. 0 Variance = "On Track", > 0 Variance = "Ahead", < 0 = Behind Schedule. =IF([Variance]@row = 0, "On Track", IF([Variance]@row< 0(), "Behind Schedule", IF([Variance]@row> 0, "Ahead"))) Can someone help me…