-
Help with Countifs & Contains formula
I am trying to create a formula to count the number of orders that are on hold. However, we have several hold types, so I just need to count any order that says 'hold' in the field. Here is my current formula. Can you help me figure out why it is not working? =COUNTIFS({Copy of Copy of LATAM Backlog Range 2}, "40 DEALER…
-
Changing draft start date based on value in a cell and the planned delivery date
I have a smart sheet with a planned delivery date of a communication and am using "T Shirt Sizing" (Small, Medium, Large) to indicate the scope and criticality of the communication. I want to calculate the date that the draft should be ready for submitted for review based on the T Shirt Size. I've entered the following…
-
Is there a solution for a v-lookup that is returning a NO MATCH for an auto number in another sheet?
This is an odd one. I have a source sheet Contract Tracker with an Auto ID column where I initially started the numbering sequence to include a suffix and then recently changed it only to be numerical. One a second sheet, I am entering the Row ID for select items from the source Contract Tracker and formulas are pulling…
-
COUNTIFS and SUMIFS formulas with multiple criteria AND a dynamic date range in a sheet summary
Hi everyone, I've had a look in the forum threads to try and find an answer already, but not sure what I'm trying to do is like-for-like with some of the suggestions. BACKGROUND: I'm helping colleagues with aggregating some of the data in their deployments sheet, which they use for managing and tracking their events, the…
-
Index/Match in different scenarios on same sheet
So far I've been successfully working with index/match to pull certain names (PM/Supervisor, Additional CC) when a certain requester is used on a sheet. Now I have a requester that is assigned to two (2) different clients, but the PM/Supervisor and Additional CC are different people per client. How can I incorporate that…
-
Access values stored in cell history
Hi guys, my idea here is to use the stored information in a cell value. Let's say that the first time this cell was changed is important to me and I want to store this value in a different column in my sheet. I would then use a formula to get this stored information, for example: =GETCELLHISTORY([info], [position]) info:…
-
Identify Most Recent Record
Hello, I am using SmartSheet to log Training Records. Every time a record is submitted, I am recording three fields: Date, Name, Topic. Based on the date, I am projecting forward the next time training needs to occur. (ie: 3 years after most recent training record). This occurs in a "Renewal Date" Column as a column…
-
Make Single Line Form Entry into 18 Separate Rows
I have created a process using Helper Columns, Formulas and Automation that takes a 1 row form entry copies it into 18 separate rows based on the Sport Column and then copies those 18 rows into a 2nd sheet data sheet and then moves the entered row (Row 1) to an archive sheet. The theory was that when someone submits a form…
-
Isolate User Entered Value in Multi select list
I have a multi-select list that looks like the below. My goal is to map form results to a PDF and I've used helper columns to turn the responses into checkboxes [formula for those: =HAS([Indicate the type of referral source]@row, "Yes, parent")]. However….I do not have the multi-select limited to my answers so that staff…
-
Parent row status formula based on child rows
Roll up formula for status I have this formula (shown below) where I am trying to get the status of the parent row automatically entered based on the child row. I'm not sure if I've stared at this too long and my eyes are crossing, but it's not doing what I want it to do. I think I've listed all the permutations below. 1.…