-
Task Health Indicator Formula (IF/AND/OR)
Got burnt out looking for my exact scenario in the forums, so looking for direct help. Trying to create a formula to automate my Health column KPIs based on task status and assigned dates. Status options: Not started, In Progress, Blocked, Complete Desired Status Colors Blue: Status = Complete Red: Status <> Complete AND…
-
How to Get a Balance Column to Show the Balance Remaining in Each Row
I am trying to create a budget sheet which shows the remaining balance for each line. Here are my columns: Order: This column has the following formula =MATCH([Row ID]@row, [Row ID]:[Row ID], 0) to allow me to move around items to better fit the budget balance for each period. Row ID: This is the auto-numbered smartsheet…
-
=COUNTIFS Metrics across 2 different sheets
Hi All, I have a metrics sheet where 4 of the 5 listed items reference 1 sheet and the last 1 references another sheet. I keep getting #UNPARSEABLE error for the 5th item. Below is my formula if anyone can help me figure out what is wrong that would be great! =COUNTIFS({Sheet - Continuous Improvement Sheet}{Project…
-
Summary Sheet Formula and Milestone / Key Task Gantt
Hello community, I am new to using the smartsheet so hoping you can help me with the below. How can I update this fomula that I am planning to use in my Summary Sheet so that it only includes count of tasks that has a start date of in the future. I want to exclude those from my count. I consolidated 3 projects into one…
-
Index Match Help
I can't seem to get this Index Match to pull all information its just pulling certain cells and I don't know why. I've tried VLOOKUP with the same results as well. Basically I have a sheet, Business Tracker, that tracks the names of Segments, the different levels of leaders, the FTE and call volumes numbers from their call…
-
Nested INDEX/MATCH formula for conditional cross-sheet reference use case
I am currently using the following INDEX/MATCH formula to return values from the sheet labeled "Tech Project List" in the reference. =INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)) I now want to expand that formula to include a condition: If #NO MATCH on Tech…
-
Sum cells based on multiple criteria with a timeframe requirement
I am working on trying to sum the time I spend across all my projects and want to sum different columns but only when they meet different criteria. Every formula I work through doesn't seems to work. I want to sum the Duration of Meeting IF the start date of a subject is within 5 days of the date in the green box that I…
-
Dynamic Unique List?
Is there a formula or work around to return a dynamic list of unique values from one sheet to another? I'm attempting to create a "Count" of projects by project manager from a project intake sheet. I'd like the report or sheet to dynamically update if a new hire PM is added to a project on the project intake sheet.
-
How can I create a helper sheet that reads totals from multiple reports
I need to create a 'helper sheet' sort of like a metrics that will collect multiple counts from multiple reports so that I can chart it easier in my dashboard. I currently have 60 reports which each have 3 counts on them, meaning that each one would need to be charted individually. I would like to be able to create a…
-
SumIfs function with a date
Hello! I have a sumifs formula that works great: =SUMIFS({Amount}, {Branch Number}, Label@row, {Request Type}, "Maintenance") My problem is when I try to add a date aspect to this formula. I want to break this number down by month, so how much was spent at a specific branch by request type and month. I added this formula:…
