-
Combining IF/Date and Possibly Index/Match
I am trying to create a formula that performs several task in order to yield one value. The formula needs to (a) look for a unique identifier from one sheet to another (index/match?) AND (b) it also needs to look at a date and if it falls within a particular month. If the unique identifier matches and the date range…
-
Formula for Red, Yellow, Green, Gray balls
Hi guys, I think my logic for the below is right, but I've got a syntax problem somewhere. Results for any "Not Started" are "Incorrect Argument Set". Otherwise it works fine. =IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End…
-
How can I move children from primary task column into new columns in the parent row
Initially the team asked for all of our project entries to be in a single column, and common subgroups be indented. Later, management realized they wanted to be able to see the subgroups as columns. I've successfully matched the subgroup row name and move it into the new column but been unable to move the contents into the…
-
Calculating the number of workdays between two dates, even if the dates are after business hours
Hi, I have a formula to calculate the business days between the date REQUESTED for a document, and the date ALL COMPLETE. It works fine unless the document is requested after 5pm and/or completed after 5pm PST. For instance, if the Request is 5:30 pm, and the Complete is 6:30pm same day, I’m getting -2, instead of getting…
-
Using the collect function and "Assigned to"
Hi all, I am using the COLLECT function (within a SUM function) to collect all values associated with tasks assigned to a specific person. For example, =SUM(COLLECT({Range of Values I am Collecting}, {Column where I have person assignments}, ="Person's Name")) This isn't working for me... any tips? Thanks!
-
How Can I Connect a string of IF/AND and ISBLANK statements Into a Working Formula?
There is probably a much sexier way to solve this but separately these 3 groups of statements actually work just fine. I just haven't been able to string them together and get Smartsheet to spit out the info for a given input. Would love to employ a shorter, more elegant solution but would be just as happy if someone can…
-
Keeping a running balance, based on a unique identifier
Hello! I am trying to figure out how to create a tracking sheet for employee Tuition reimbursements. We allow an Annual Funding Maximum, and each time the employee submits a request, I'd like a formula to show their current available balance, based on their annual maximum. We have employee ID's as a unique identifier. The…
-
Find duplicates based on multiple criteria
Hello everyone, I need some help with correct formula to find out duplicates. Is there a way to find duplicate records if case number, client's name, from date, and to date are the same? The first and last records are duplicate but how do I set up a correct formula? I currently only have case number and name columns in my…
-
How can I display the last value from a column into certain cell?
Hi guys, I would like to display the last value from the column "Project Phase" into a cell (in this case I would like to implement it at the first cell of the column " Project Phase". Regarding to the screenshot, this would mean that I would like to have the text "Offer Phase" to be displayed automatically in my cell at…
-
Inspect Cells Until Contents Found, Then Copy to Another Cell
I'm trying to create a column ("Latest Site Visit Notes") on my sheet that fills in with the latest site visit notes. We have existing columns for "Construction Supervisor Visit 1 Notes" thru "Construction Supervisor Visit 20 Notes". Our hundreds of projects all have various quantities of site visits performed so far. I'd…