-
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…
-
How do I avoid the cross sheet cell reference limit of 100,000?
I have several formulas that are critical to one of my dashboards. They reference a master sheet that grows daily in number of rows. I end up getting the cross sheet cell reference limit warning about every 15 days. Is there some way to easily mitigate this? I'd like to avoid reducing the size of the master sheet so…
-
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…
-
My SUMIF formula stopped working and I don't know what's wrong!
So I have this formula and it was working fine, but I updated the sheet with new people - new names in the intern field and so changed the formula to align with the new names. Now it's showing as an Operational error, but I have no idea what's wrong - anybody have an idea??