-
JOIN COLLECT two variables
I am trying to find a way to collect two different variables from two different ranges {courseyear} and {regstatus}, then join them together to determine the year and registration status in a single cell: Here is what I would like: =JOIN(COLLECT({courseyear}, {id}, $ID@row, ({regstatus}, {id}, $ID@row)), "-") Output:…
-
How to reference two columns on a different sheet for a formula on another sheet using countif
Hello, Im creating a rollup page for a dashboard and need to create a formula to count 2 separate columns. Basically i want the amount of "pilot" studios that are "validated" or "not validated. Here is what i currently have: =COUNTIFS({Studio Network Stack Submissions Range 2}), "not validated"),{Studio Network Stack…
-
Formula to deduct calender days from a date, holding weekends in account
for example: date is 09/06 - need to deduct 10 calenderdays, the date returned may never be saterday or sunday. the outcome must be 01/06 - since 10 calenderday returns Sunday 31/05 - which is not allowed.
-
Need help with 'If , Then' function to subtract Duration Values
I'm setting up a Construction Schedule with Variance calculations as the 'Near Term' baseline solution that's been quoted as coming for a few years now has not materialized yet. Unfortunately after searching the forums I'm not seeing syntax examples that work for what I need to do. It would be helpful for me at least to…
-
Multiple IF AND OR in same formula
Hello, I`m trying to do something like this : 7 Status IF(Action@row = "", "", IF([% Complete]@row = 1, "Complete", IF(Owner@row = "", "Not assigned", IF(AND([End Date]@row = "", [% Complete]@row = 0), "Cancelled", IF(AND([% Complete]@row < 100, TODAY() > [End Date]@row), "Overdue", IF(OR([% Complete]@row <> 0, [End…
-
Roll up of assessment data with conditions
I need to be able to roll up associated transmittal #s to 1 Transmittal and if the assessment status is Not Compliant for any of the Compliance Assessments then Transmittal 1 should equal Not Compliant, Transmittal 2 = Not Compliant, Transmittal 3= Compliant
-
Issues with @mention
Hey Community! Has anyone had issues with users being able to use the @mention feature? I have a user who cannot access this on one of our sheets, by typing the @ symbol, or by selecting in comment pane (it isn't there), but that can @mention in other sheets. Any help you guys can offer would be greatly appreciated. Note:…
-
How to link the health column to show an accurate status of at risk tasks.
Hi All, I am looking to find a way that I can link (create a formula) so the health column shows a traffic light system for the status of the task. I want to aim for this to detect the percentage of completion of a task in relation to the finish date. For example, if the finish date is only a couple of days out and the…
-
autofill of formulas is not working when adding more than one line of data without saving the sheet
I have a sheet with some formulas. If I add a new row of data ,the formulas are autofilled as expected. If I add another row of data, the formulas are not autofilled. If I save the sheet after adding the first row of data, the next new row will autofill the formulas. This means, I have to save the sheet after adding each…
-
Vlookup with multiple rows of same search value
I am trying to convert an excel sheet that has multiple rows of the employee's name with one performance objective per row. So we have this: Sue Smith/Objective 1 Sue Smith/Objective 2 Sue Smith/Objective 3 I want my Smartsheet to be like this: Sue Smith/Objective 1/Objective 2/Objective 3 Trying to wrap my head around how…