-
Logic based formula help needed if someone could help please?
I am trying to make my sheet derive a priority based upon the responses gathered from a form that is completed. Users will be selecting from a dropdown list of categories and, based upon their answer I want a priority to populate in another cell. I am not experienced enough to figure out how to build the IF formula (or…
-
Formula Referencing Another Sheet
Hi, I wrote a formula and each of the column will be referencing from another sheet whereas it shows unparseable. Can you give some suggestion on how to modify? Formula is in Sheet A =COUNTIFS({Person Accountable}:{Person Accountable}, CONTAINS(@cell, "System Manager"), {Done}:{Done}, @cell=1,…
-
Unflag after marked completed
Hi, I am new to formulas. What can I use for the following: I currently have this formula to flag an over due task: =IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), 1) The formula on top works fine, but when the user clicks on the completed checkbox the red flag still appears. I want the red flag to disappear when…
-
COUNTIF with keywords
Hello, I am trying to set up a formula to count a cell if it contains a certain keyword (e.g. "communication"). The cell itself won't be only composed of that word, but will just contain it. My formula so far is =COUNTIF([Job Classification]:[Job Classification], CONTAINS("communication", [Job Classification]:[Job…
-
Past Due Date
My Smartsheet has an "Estimated Due Date" column in date format. To the right of it, I have a column "Past Due Date". Based on Today's date, I'd like the Estimated Due Date to be compared with Today's Date. If the Estimated Due Date is in the past, I'd like a "Y" or "Yes" to be returned in the Past Due Date column. Is…
-
How to Use SUMIF Function for Sheet Summary
Hello! I am trying to use smartsheet to support budget tracking. I'd like to summarize the amount spent and section by our spend category. So if my Cost Center Category is equal to "Paid Media" I would like a sum of all rows meeting this criteria. So my range headings are Cost Center Category and Amount, my Criterion is…
-
TODAY() Seems to cause #BLOCKED message
I have researched this issue and I have tried several solutions. I have set automation to insert the current date into my project schedule sheet, then automation to lock rows and then automation to unlock rows. This did not clear the issue. I have created a separate sheet with automation to insert the current date into the…
-
Count the number of duplicate unique numbers
Hi, I have a column of unique numbers that I would like to track if duplicate entries occur. If a duplicate entry occurs I would like to pull the unique number into my metrics sheet and track it in a separate sheet; ideally id like the new column in the metrics sheet to include the duplicate unique number as well as how…
-
automatically update cell based on other record
Within the same sheet, there are two row types : Header and Detail. There is a Report Name Column that group these record together. Based on the image below. If record1 (Report01 and RowType = Header) has a Status = Approve, I would like all detail records under the same Report Name (Report01) to have Status = Approve…
-
Vlookup from Created Date column
Hello, I'm having trouble when I use vlookup from a column typed as "created date", every time a date is created after 6pm, my vlookup returns the following day, as shown in the screenshoot. Is there a way to fix this?
