-
Use JOIN/COLLECT to return the first x number of characters
I am using JOIN/COLLECT to return a list of items matching a criteria is a separate sheet. Items are returned in a single cell. The formula I'm using is: =JOIN(COLLECT({Item Number}, {Date_Complete_Helper}, ="No", {ExtTestHelper}, ="No"), SUBSTITUTE(Lots39, "-", "")) (the substitute is used to provide a line break between…
-
Help with Status driven by date columns
Hello, I am trying to automate my status column that is driven by dates. Date columns: Deployment Start, Deployment End, Planning Start, Planning End Statuses: Planning (this is default) status; Execution, Complete I'd like the status to default with Planning if dates are blank or within the Planning Start & End, Execution…
-
How to sum blank cells?
I have created an assessment where each question needs to have an answer in order for the scores to calculate correctly. This assessment has a form I can share out for others to complete. I am trying to use the Red, Yellow, Green symbols to tell me if a form is 100% complete (Green), 80% complete (Yellow), or <80% complete…
-
Auto fill form drop down list field in one particular scenario
Hi. I currently have a column that can be either Capex or Opex depending on the chosen item from drop down list on a form. I have been asked to amend this so if another drop down list value is "X" then column Opex/Capex is to be autofilled as "Opex" else user the value entered on the form.... Any pointers for this please?
-
is there a formula to identify the source sheet for the maximum value?
Hi there, we are tracking building compliance inspections for a range of buildings and inspection types. I will write a formula which checks three lists of dates to return the most recent inspection date for a particular building. Is there any way to have a formula pull through the name of the sheet on which it found the…
-
Random numbers / Random form questions.
I'm looking for a way to generate questions via a form. Say we have 100 questions on a grid split into 10 sections. In an ideal world I'd like to pick 3 random questions from each section and then have that listed on a form. One way could be to have a random generator of sorts and have it randomly assign a number to each…
-
COUNTIFS with Specific Dates
I'm working on a Pre-COVID and COVID list and could use some help. Can someone take a look? One example: We need to COUNT how many times TRB was used during the dates of January 1st, 2020 to March 13, 2020. =COUNTIFS(Division:Division, CONTAINS("TRB", @cell)), ([EventDate, >DATE(2020, 1, 1):EventDate, <DATE(2020, 3, 13)])
-
Automation - Alert Someone and then reply to the same email chain when row is updated
Hello experts, I am trying to set up an automation workflow with Alert Someone. When new Row is added - Alert Someone (Row is added with information sent via Form) - works fine I update the Row(is a task) with further information - automation rule to Alert Someone when changes are made is applied. - works fine too Each…
-
Contains with Multi-select column
I have a column that is multi-select for our systems. The values in this column are X, Xi, S, SP and a few others. I am trying to count the number of rows where the value "X" is selected. Sometimes it may be only X, other times the column value may have both X and Xi. When i use contains, it is counting everything in the…
-
Counting using child rows
I have sheet with several parents and children Day 1(Rows 1-20) Time A (rows 3-5) Time B (Rows 7-9) Time C (Rows 11-20) Day 2 (rows 21-48) Time A (rows 22-27) ETC ETC Rows could be added to any of the time slots at any time. I want to keep a real time count of how many are in each time slot If I want a count t of "Day 1…