-
Health of Task or Project using % Complete & Finish Date - Sanity Check
I found something close to what I am looking for here: NEED HELP: Health Formula based on start date, end date, and %100 complete needed. — Smartsheet Community This formula doe s a great job of flagging the dates in relation to time left. However, I am looking for a health check that can take into account both percentage…
-
If then formula with different sheets
Hello, I am looking to create a formula that looks at the Tag Name/ID from these two different sheets. If they are the same, I want the Date Received in Full to show up in the Received by Van Ert column for that Tag Name. For example: The Tag ID in line one on the first sheet is 410-AE-1391B. The Tag Name in line 581 on…
-
Countifs for a range based on checked box in a different column
I am working on a training matrix where our team tracks the operator's training status for multiple assays. We also have a columns for different suites of trainings needed per job title where if the assay is needed for the job title, the box is checked. The ultimate request is to show % Complete Trained based on the…
-
Combining AVERAGEIF and AND Functions
Hi All, I am wondering whether it's possible to combine the AVERAGEIF and AND functions together to meet 2 criteria. In my case, I'm averaging time - {X} is in minutes hence the division by 60 to convert to hours and the 2 to round to two decimal points. The below works as expected. =ROUND(AVERAGEIF({X}, {X} > 0) / 60, 2)…
-
Join/Match/Count Criteria
I have a sheet that is populated by another sheet. And in this populated sheet - I'd like to count how many times certain criteria show up (two specific data points). I assume I would need to join the two columns (i.e., Last Name and ID) and then if these two items show up together again, I'd like to have a count of these…
-
Using IF formula to reference any change in cell (adding a value)
Hello Experts! I was wondering if a formula like this is possible: =IF([Confirmation #]@row = "any value", "Confirmed") When a confirmation number is added to a cell, that means the item is confirmed. What can I put in place of "any value'? I am just not sure how to do this in the context of my larger status formula:…
-
Planko Status Column - is this possible?
Sheet 1: Col 1: Numbers in row sequence, i.e. 1111, 1112, 1113, 1114 Col 2: Status: Show / Unavailable Sheet 2: Col 1: Last Name Col 2: Number (index/matched from Sheet 1 based on Status Columns) Col 3: Status: Show Sheet 2 is a manual row entry sheet, and what I’m hoping to achieve is that when the status changes in Col 3…
-
Status light, nested IF statement
Hello everyone, I'm looking to find some help with my status light formula. My columns are as follows: • Date Due (date column) • Progress (drop down column with: "Not Started", "In Progress", "Complete" as options) • Status (drop down symbol column with "Red", "Yellow", Green", "Gray" circles -- to become a formula column…
-
Highlight duplicates across multiple columns
I think I already have the formula working for my Number column, but I would also like it to check additional columns "Number Field 2" "Item Number" for duplicate values. Not just checking for duplicates down one single column but occurring in any of the fields and highlighting those occurrences. I hope that explains what…
-
Percentage (%) complete formula
I'm using this formula suggested in another thread to calculate the percentage complete for particular tasks: =(Today()-StartDate)/(EndDate-StartDate) What I'd like to know is how to cap this at 100% because I don't want the percentage to keep increasing the further past the completion date we go. Any ideas?