-
Nested if /or formula
Hello, I am looking to have a status bar (the one with empty, half, three quarter and full) look at two columns and give a result. I have percent complete and a "Done" checkbox. the percent complete is by date, so when the date gets closer the status bar shows progress. Here is the formula I am using to do so..…
-
Max Function but with Text
Hello Smartsheet Community: I want the Overall column to look at Issue A, Issue B, Issue C and Issue D and return the highest level. The levels are No Deficiencies, Low, Medium and High. Is that possible with text? See screen shot. I know I can do this if I made High = 3, Medium = 2, etc. and only had numeric values in the…
-
COUNTIFS functionality for multiple entries
New to SmartSheet and I promise I have been looking around for an answer, but without any luck so I thought I would throw it out to the community... I am looking to sum up all entries in column in a summary total at the top of my sheet, but the formula I have is only calculating where the searched for text is alone in the…
-
Unparseable Error
Good morning, I'm new to Smartsheet. I'm getting an error when I add the following formula. I'm trying to get it to return the current project phase that is less than 100%. Do you know what I'm doing wrong? =IF([% Complete]37<100%,[Task Name]37, [% Complete]54<100%,[Task Name]54, [% Complete]75<100%, [Task Name]75, [%…
-
MAX and COLLECT to return most recent value not the highest
Hello! I am currently trying (and failing) to create a cross-sheet formula that will look for the most recent date for a given lot and then return a specific value from that date. Sounds simple enough right? I have one column on my metrics sheet that correctly pulls the most recent date for a given lot: =MAX(COLLECT({Assay…
-
"IF" formla and Red/Yellow/Green
I am putting together a sheet that will be used as a template. There are 3 columns (Date Assigned and Date Due and Status). Status states Complete, Not Complete, and Not Applicable. There is another column that has the Red/Yellow/Green bubbles that should automatically update (red if late, yellow if within the date range,…
-
Cumulative Column Formula with Exclusion
I have a cumulative column formula created that cumulatively adds up months, if we're in May, it will add Jan-April, if we're in June, it will add Jan-May, etc. There are some metrics the customer wants to be excluded from this formula and instead, have the formula provide the last month entered. I created a checkbox…
-
Change status if progress bar changes (help w/syntax)
Hello I want the status to change if the progress bar is changed. I tried a formula but only have it half working. =IF(AND(Progress1 > "Empty", Progress1 < "Full"), "In Progress", IF(Progress1 = "Full", "COMPLETED!") This is the formula. I want the status to show either 'Not Started' , 'In Progress', or COMPLETED! based on…
-
Flagging a double booked room
Hi Guys I have a tracker that my team use to manage their daily tasks. However, due to COVID there can only be a specified number of people in a room at a certain time. Often rooms have been double booked, resulting in work being delayed. I want to highlight whether a room has been double booked on a specified date by an…
-
What formulas to use in a timesheet solution to SUM up values
Hello, I have used one of the great solutions that has been posted by, I believe, @Paul Newcome relating to tracking and working out time/duration of hours in a HH:MM format. There are 2 columns to achieve this - the first uses a formula to work out the numerical duration: =((VALUE(LEFT([End Time]@row, FIND(":", [End…