-
Smartsheet IF(AND Issue. Will not work across two separate months
Hi, I've been using Smartsheet for around 6 months and would class myself as intermediate. However when it comes to Formula's I'm certainly a novice! I have a Smartsheet that acts as a Hotel rooming list. Users can load their Hotel Check In Date and Check Out date which then prompts a formula to count up the nights that a…
-
Index / Match Multiple columns from another sheet
I tried to use index/match to reference multiple columns from a resource sheet. I can't seem to get the formula correct. My table (resource) has a list of teams with start times, parking information, notes, etc. The table includes all the team information. When I add a name to the new sheet, I assign that name to a team.…
-
Return Most Recent Date and Other Column Value or Only Flow Most Recent Entry into Sheet per Account
I need to be able to return the most recent value for the Created Date column and the corresponding Discussed column text. Currently, I am using an IF, COUNT, MAX, CHILDREN formula that I found on the community to return a MAX date within a grouping of rows but I can't figure out how to also return the Discussed value that…
-
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…