-
How to handle a very long list of (multiple select) choices in a form
I am new to Smartsheet and using it to replace old InfoPath forms. One thing I cannot find an elegant solution for is expanding fields/sections. Basically, I want to send an instructor a form that asks which courses they would like to teach next semester. There are hundreds of courses. I will eventually want to use this to…
-
Extracting string from a cell
hi all, would love some help here for this-- I have a column that contain these strings in a cell: "relates to ABC-25277, relates to AF-4329" "is caused by ABC-33334, relates to ABC-33362, relates to AF-10303" I want to extract the "ABC-25277" from the cells and populate into a new column.
-
Calculating Rolling Average Using AVG(COLLECT())?
Hi, I am attempting to create a rolling average to replicate a "trend line" one might see in excel graph. Having trouble visualizing the formula I might use, but landed on AVG(COLLECT()) as a plausible option. Hoping to poll the group. I've attached a photo of the sheet. Essentially I want an average of all preceding…
-
Smartsheet formulas
Hi, Can anyone suggest me with a formula to carry out the following function: - If RISK(H/M/L) CAT (0,1,2,3) column states "2" then Start on site date column needs to insert a date 12 weeks prior to the design required date. If RISK(H/M/L) CAT (0,1,2,3) column states "1", then Start on site date column needs to insert a…
-
Smartsheet formula........
Hi, Can anyone please help me with the third part of the formula attached below? I want it to check if a column in a different sheet has a check box against the item.
-
Smartsheet formula...
Hi, Can anyone please advise a formula to calculate the workdays between two dates? I am using the formula below but I don't think it calculates the work days between two dates (Monday-Friday) =COUNTIFS({Status}, Open, {Proposed Close Out Date}, >=TODAY(), {Proposed Close Out Date}, <=TODAY(14))
-
Count of Cells less than a Date
Hello, I am working on a COUNTIF Formula which is not working. The whole formula which works: =SUMIF(Workstream:Workstream, ="Asset Mgmnt", [HIDE ME % COMP]:[HIDE ME % COMP]) / COUNTIF(Workstream:Workstream, ="Asset Mgmnt") Where Workstream is a drop-down column, and Hide Me % Comp is a % column. Focusing on the second…
-
Less Than Date Comparisons with Blank Cells
I have a column "Deliverable Past Due" that should return a Yes if any dates in the stated columns are less than today. The issue arises when many of the date columns are blank. The formula recognizes blank cells as being in the past and always returns a "Yes". I tried adding ISDATE to the statement, but then it always…
-
Any way to use a cell value as a row number in another cells formula?
Hi all Anyway to get the row # from a cell value and use it in a formula? Example this works: IF(COUNTIF([Assigned To]$974:[Assigned To]@row, [Assigned To]@row) > 1, "DUP”,1) Now let's say Column1 row 23 contains the value 974 I would like to do this: IF(COUNTIF([Assigned To][Column1]23:[Assigned To]@row, [Assigned…
-
Automating a way of understanding time difference when start and end time are in the same column.
Hi everyone, We are looking at using a smartsheet solution to understand how much time employees on our production lines are spending working on certain lines. I created a separate sheet for each production line (we have 12 production lines) and I am using QR codes scanned via a smartsheet form (Quick for employees to…