-
Trying to use COUNTIFS and keep getting UNPARSEABLE error
Hi, I've entered this statement: =COUNTIFS(PMO Project RAG Status Range 5, <DATE(2019, 12, 31), (PMO Project RAG Status Range, "Corporate Support - Corporate Finance") What I'm trying to do is count the number of projects with the designation of Corporate Support - Corporate Finance that will be delivered in 2019. The…
-
Progress Bar Date Automation
Hi All, Could someone help me figure out a formula for the progress bar column style. My goal is to determine how close (by progress bar visual) are we to the "target end date" of a task. For example, my start date is 01/09/2022 and my target end date is 30/09/2022 and the actual date of today is the 29/09/2022 so we would…
-
How to subtract quantities from one sheet via forms to another sheet with total quantities
My issue is this: I have a master sheet with material types (A1,A2,A3 etc..) and total material quantities (#'s). The other sheet I am using is updated via forms. The form is used for distributing materials and requires a material type, and material quantity before submission. Once submitted I would like the quantity to be…
-
Identify Quarters based on date AND add text if cell is not a date
I am wanting to identify a quarter based on a date and have that formula working, but I also want it to return a value of "TBD" if the Start Date is not a date AND return a value of "Dates Invalid" if the Start Date is outside of the current year, which in this report is 2023. How can I adjust the formula below to…
-
Average formula with error should give a blank cell
I used this formula below to give me the "DOC % Complete" but it is missing something. If the cell has “Cancelled” the Docs % Complete column should be blank but right now is giving me 50% =IFERROR(AVG(ISDATE([Design Circuit Recommendation Actual]@row), 0.5) + IF(ISDATE([Design Velo Recommendation Actual]@row), 0.5), "")…
-
Combining/Summing Contacts from Multiple Cells in a Single Cell
Greetings, In a Contact Column, and in a ancestor/child relationship, I would like for parent rows to summarize/list all persons in the child tasks. Is Smartsheet able to perform this action? E.g. If the child records list: * John D * Claire H * Emily G I want the parent to show: * John D, Claire H, Emily G. I have tried…
-
LINKED CELL FORMULA
I have a template sheet which is used for many other sheets but my managers keeps moving the goalposts for part of the reporting meaning i have to update a formula on the template then copy and past into the other 18 sheets currently in use. Today i set up a new sheet i thought i would try linking the formula cell to the…
-
Adding months automatically
Hi all I am trying to automatically add a 6 month review date to a start date, it is working fine until a start date is past June, to which I get a #invalid value error. The formula I am using is: =DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)) So 1/6/19 becomes 1/12/19 but if I try with 1/7/19 I get #invalid value instead of 1/1/20.…
-
How to enter specific text if the cell contains a partial match?
My If statement: =IF(OR([Task Name]@row = "Discovery", [Task Name]@row = "Analysis", [Task Name]@row = "Build", [Task Name]@row = "Test", [Task Name]@row = "Deploy", [Task Name]@row = "Design"), "PMO", IF(OR([Task Name]@row = "PER", [Task Name]@row = "PRT", [Task Name]@row = "CK1 RTS", [Task Name]@row = "CK2 RTB", [Task…
-
Impossible to visualize groupings more than 2500 rows...
Hi, Im searching over the internet a variety of things (data) from a variety of types (countries, format, source, etc) and some of them are validated (true) and some dont (false) and inserting this information in a Smart Sheet page. On the other hand, I have this REPORT, reading from that sheet i have just mentioned, and i…
