-
Please help with the following formula to return '% Completion planned' with conditions:
I have the following columns for tasks: Planned start date Due date Planned duration; calculated as =1 + ([Due date]@row - [Planned start date]@row) Days remaining or overdue; calculated as =[Due date]@row - TODAY() % Completion planned; calculated as = 1 - ([Days remaining or overdue]@row / [Planned duration]@row) I am…
-
Index Collect Permissions
Hi all, I have a Smartsheet which looks up values from another sheet using Index Collect, which is working well. However other users with Smartsheet license and with a free license will be loading the sheet who don't have access to the source data sheet. I don't plan in opening the this (the destination sheet) hoping that…
-
Date formula for Last month, Last Quarter, Last year
I'm looking to add some formula to my summary sheet and the requirement is for analytics for Last month, last quarter and last year - so excluding current month Examples if: current month = May 2020 last month = April 2020 last quarter = February, March & April 2020 last year = May 2019 to April 2020 inclusive These would…
-
How to mark column with 1 or 0 if the month is in a date
Hi, I am trying to track whether or not someone has been active in a month based on their Last Login date. I created the formula to mark a cell as 1 if the month is in the Last Login date or 0 if it is not: =IF(MONTH([Last Login]@row), 1, 0) But the problem is it gives an #INVALID DATA TYPE error if it hits a blank date.…
-
How do I count 2023 Q1 items that are not specific request statuses?
Here is my formula: =COUNTIFS([Plan of Record]:[Plan of Record], FIND("2023", @cell) > 0, [Request Status]:[Request Status], NOT(CONTAINS("Cancelled", @cell), NOT(CONTAINS("Merged", @cell), NOT(CONTAINS("Rejected - Insufficient Value", @cell), NOT(CONTAINS("Rejected - Duplicate", @cell)), [Planned MVP Launch Date]:[Planned…
-
INDEX, DISTINCT, and MATCH formulas
I am trying to put together a list of our project managers and which business units each of them are responsible for. I need it to be in a certain format because right next to each of the business units, I am going to have 3 COUNTIFS formulas to count different things based on the project manager and business unit. I…
-
Collect and display associated numbers
Hello, I'm looking for a formula to use to reference another sheet and collect and display the different User Story numbers associated with and OMNI #. I was thinking maybe VLOOKUP or INDEX with COLLECT or MATCH but I can't get it to work. Here is the data set: So if the formula works correctly, the row with OMNI002800…
-
Sheet Summary not matching actual with multiple criteria
Hello, I am new to smartsheet formulas and have a problem that needs to be solved. I am trying to capture a count of sites that have 2 criteria. Completed in one column and counting if the dropdown is "Full Install" "Pre Cabled UPS Only" and "Revisit". On my sheet summary, I get a total of 14 using the below formula but my…
-
Why am I receiving #INVALID OPERATION error in some of my sheets
I have multiple sheets with the same columns (Health, Task Name, Start, Finish). Note that the Health column is a symbol (red, yellow, green, blue). In my summary sheet, I am using the following Index Match formula to search for Task A in Task Name column and spit out Health. =INDEX(Health:Health, MATCH("Task A", [Task…
-
Numerical "Priority" Method
Hello, I am in need of a method for assigning priority of a submitted row 1-10. If 1 is selected, the previous 1 needs to become a 2, the 2 becomes a 3, etc. The column needs to recalculate when a row is complete and the 'priority' column becomes blank. I don't think this would be possible without using many columns to…
