-
Data Migration/ Data Shuttle between 2 sheets
I have 2 sheets Sheet 1: Projects pending approval Sheet 2: All the projects that have been approved I want to build functionality where when I check an approved box in Sheet 1, it will automatically push to Sheet 2. There are a couple issues/ complexities. One of the biggest issue is that the Sheets have different column…
-
Adding Days to a Date Based on a Condition
Hi I've trawled the forum for previous questions but can't seem to see one similar to what I need. I have a date column that I want to auto calculate based on a condition within another column. If [Corrective actions to be completed] = "Immediate" or "5 working days", take [Corrective action chaser 1] + 11 days, if…
-
formula for +40 days after Q
hello, thank you in advance i am looking for a formula to auto calculate 40 calendar days after end of quarter. which does not include holidays or weekends. thoughts? Thank u
-
Dropping day from date format
Hi Smartsheet Community, I need to report a date field in a report, however the client only wants to report mm-yyyy instead of the full date (dd-mm-yy). For example, in the fields below they want to report 09/2020 and 04/2021, and 08/2020 and 08/2020 respectively Is there anyway to drop the day component in another column…
-
Populate Q1, Q2, etc with IF function not working
Hi there, I currently have a formula that is working for Q1 & Q2, but for some reason, it is not working for Q3 & Q4. I am at a loss as to why this is not working properly. Any ideas? =IF(MONTH([End Training Date]@row) < 4, "Q1", IF(MONTH([End Training Date]@row) < 7, "Q2", IF(MONTH([End Training Date]@row), 10 <"Q3",…
-
Multiple users in dropdown formula
I'm trying to countifs a user is on target. My sheet column is a multiple selection column, some rows have multiple users selected. When I create my formula, it doesn't count if said user is sharing a responsibility with someone else. Here is my current formula =COUNTIFS({2021-2022 COMPLIANCE WORK PLAN Person Responsible},…
-
Formula for percent match to a multi select column
Hi, I'm working matching mentors and mentees. We would like to match them based on attributes they both selected in a multi select column. As in, choose your top three competencies. Now I would like to make a formula to determine the percent match of a mentee to a mentor's selection. That is Mentee 1 selected A, B, and C.…
-
Formula Question for Percentages into Risk Status
Hello, I am trying to have the formula below also reflect that if the percentage is 0% it automatically has the risk status show as blue. Could someone please assist with this? =IF([% Complete]@row = 1, "Green", IF(AND(Finish@row <= TODAY(1), [% Complete]@row < 0.51), "Red", "Yellow")) Thank you!
-
SUM IF formula upon specific criteria
Hello, I was looking for right formula to SUM IF upon specific criteria (KMF662), and how many fuel has been used over they May (sum only May). I want to apply this formula to each month separately, according to a separate car. Thank you for your answers in advance.
-
Summary reporting with conditional formatting
Hello! I'm trying to build out a way to dynamically report on 58 state/territory sheets. Ideally, this report would have the total number of rows in the state sheet, plus the number of rows that meet two separate sets of conditions, with conditional formatting applied. I've tried doing this two ways: By using a sheet with…