-
How to make synchronized dropdowns?
I would like to learn how to insert in my forms a dropdowns synchronized with a column from other page. It would resolve a lot of mistakes introduced in the forms. Thank you!
-
Link to First Row With a Blank Value in a Specific Field
I have two sheets, and I wish to link to a value in one sheet from a cell in another. Specifically, I want to include the value in Milestone, 2 in the 1st screenshot, as it is the first value with a blank date in Baseline Completion Date. The value would be linked to 6, 3 (Stage) in the 2nd screenshot. I tried using an,…
-
What formulas help join people, teams, AND roles (person may have >1 team and >1 role)?
Stephen Covey says begin with the end in mind. I want to create views of people by scrum team and role as well as by role and scrum team (end result mock-up pictures below). It would be easy if EACH PERSON was only on 1 TEAM and only had 1 ROLE, however, there are other scenarios such as: * 1 person on 2 or more TEAMS as 1…
-
Calculating percent of a percent in an =IF(AND formula
I'm trying to write a formula that says if one cell is less than 30% of another cell, then note the status as "At Risk." =IF(AND([Percent Complete]@row < [Timeline Progress]@row, [Percent Complete]@row = [Timeline Progress]@row * 0.3) / 1, "At Risk", "No Value")
-
Very Simple MONTH Function
Hi All I know this seems like a waste of a question but I really don't understand why the MONTH function doesn't work My month is formatted to UK DD/MM/YYYY and I have entered =MONTH([Month]) and it doesn't work and either shows INVALID DATA TYPE or INPARSEABLE Yep, I know, should be simple but for me... it's not Thanks…
-
Trying to use AverageIF.
Hello I am trying to make a formula that counts the average amount of time an active (open) issue takes, whilst segregating it by priority (low, medium, high). To count the total amount I used this formula: =SUMIFS({Time to fix}; {Solved}; 0; {Priority}; "LOW") However I want to make a new formula that counts the "time to…
-
sumif function across multiple sheets
I am trying to build a roll up sheet that pulls criteria from multiple sheets, into one master document. I keep having an issue when I update the formula range from one sheet, it updates all the ranges when I don't want it to do that. Here is an example: For each FFE column, I have a formula specific to each Item group…
-
Deducting months from date not returning correct year
Hi - Can anyone help please with deducting a number of months from a date and get it to return the correct year? I have a contract expiration date column and a notice period (in months) column. I've used the below formula, but where I have a date of 31/03/2022 (Contract Expiration Date) and a notice period of 3 (Contract…
-
Overall Status as a percent score (factoring in percent complete, weighted average and due dates)
Hello, I'm trying to calculate a department's overall status based on a percent score. I think I need to use percent of completed deliverables, weighted average of deliverables and deliverable due dates within that area. Any words of wisdom? I'm working on a project with various departments that each provide an arbitrary…
-
Pulling monthly numbers formula
I am trying to use a smartsheet to report the number of alerts received during an entire month. There are 9 types of alerts and I need to know how many we had for a specific month. The information is being pulled from another smartsheet log which has dates going back to 1/1/2020. The below metric and formula I created is…
