-
Exclude canceled tasks from the automatic calculation of the parent's % complete
Hello everyone, I'm working on a project sheet (with dependencies enabled and the Gantt view) and I'm looking for a way to exclude canceled tasks from the automatic calculation of the % complete on parent rows. My requirement : When a child task changes to “Canceled” status, I want it to no longer be included in the…
-
Counting and Position
I am looking identify a records position within a count. Meaning if I have a CountIf that comes back 10 records I would like to know if I am on the 1st record or the 5th record.
-
Using SMALL + COLLECT to rank items by due date — duplicates showing the same row
I'm building a "Top 4 Critical/High Priority" tracker on a dashboard sheet that pulls overdue items from a RAID Log using INDEX/MATCH/SMALL/COLLECT. Here's my current formula: =IFERROR(INDEX({Task Title Ref}, MATCH(SMALL(COLLECT({Due Date ref}, {Due Date ref}, <=TODAY(), {Priority Ref}, OR(@cell = "2-High", @cell =…
-
How to enlarge cell formula reference limit
I have a sheet with 5000 rows and 30 columns. with 4 columns of them are formular columns. How can i reach this limit for 25000000 formular cells reference? How can i enlarge this limit? I need those formular to link some data from another sheet as there is also Max cells limit for one sheet. How can I join those data…
-
Formula to Automatically Calculate Remaining Budget Based on Multiple Conditions
Hello Smartsheet Community, I'm trying to build a formula that automatically calculates the remaining budget for a project based on several conditions, and I'm running into issues with the logic. Scenario: Each row contains: Approved Budget Actual Cost Project Status (Not Started, In Progress, Completed) Department I want…
-
Matching Values from two multiselect columns
Hello, I have in an intake sheet two multi-select columns - Carriers and ASLOBs. In another reference sheet I have a matrix where I have combinations of Carriers and ASLOBs with a matching Operating Company. What I am trying to active is: Match first value from Carriers column to first value from the ASLOBs column and then…
-
Report Inquiry
Does anyone know how I would organize a report sheet by date instead of by project number ? I wanted it to be in date order instead, oldest - newest. Any help is greatly appreciated
-
Hey! This is the Report Format I am hoping to have auto populated through 3 forms multiple times a day. I would love some help building this form.
-
AI for formula is not working
I would like to make the following into a formula, and tried using the formula AI option, but can't get the formula to work! "If the number in Site column (on 123 sheet) matches number in Site Number column on ABC sheet, and meeting date on ABC sheet is in the past display "1"
-
Average Resolution with Date
I have my average resolution for each ticket that is opened, and then i want to reach a summary sheet for each quarter that gives me my average resolution based on the quarter by month - i'm using my resolved date column so its in a date format. This is the formula that I am using, however i keep getting errors This gives…