-
Trying to pull data into a cell from one column based on criteria from another column
Hello everyone, I've built a project schedule and I'm trying to pull only the task that's "In Progress" into the parent row of that phase. So basically my formula needs to look at the children in the status row, find the one that states "In Progress" and pull the data in the Task column from that same row into another…
-
How do I check for duplicates within several columns and check a box
Hi All, I'm making a grid for staffing events. Basically each event can have up to 3 staff members. I want a formula that will check for if there are multiple events on the same date and if a staff member is accidentally double booked. If they are double booked, to check the checkbox so I can run some conditional…
-
Contact List using DataMesh & Concatenate
Hi, I have added 2 columns to a data sheet, one for Analyst Name and one for the Analyst Manager. I have brought in the email addresses of both of these by DataMesh. I want to be able to apply a current user filter to a certain column which would enable the Analyst and their Manager to only see the rows that are applicable…
-
Not so pretty IF-function (works) but needs makeover
Keeping track on vacation time I need to convert dates to weeks and then again to an overview with checkboxes. In order to get a list of weeks [c_weeks_listed] (later in use with FIND) I have come up with the following IF-formula which works but will become too complex if expanded further. Any ideas for a more powerful…
-
Counting Criteria that Overlaps
I have a multi select dropdown column where users can select phases of a job they have experience in. For this example, let's say they have the options of "Pre-Execution", "Execution", and "Closeout". I want to total the number of users with "Execution" experience. Using "=COUNTIF([dropdown]:[dropdown],…
-
Calculating the average of a column based on specific criteria in another group of cells
Hello everyone, I am trying to calculate the average of a column based on criteria found in another. The catch is that I don't want to specify the specific text of the other criteria as it is going to be based off of an ever expanding drop down/list of different values and I want to minimize constant manual intervention.…
-
Forms and Blank Rows
Hi, We using a Smartsheet form to capture Covid19 Screening Questions. Once submitted the person has their temperature recorded which is manually captured against the record. The problem I am experiencing is that the person capturing the temperature sometimes accidentally inserts a blank row into the sheet which results in…
-
Formula for counting past end/start dates and checkbox left unticked
Hello, I'm trying to create a formula to flag if a start/end date is in the past and the started/complete checkboxes haven't been ticked. I have conditional formatting to show this but I want to count how many red flags I have across my sheet and I understand I can't use formulas to count conditional formatting, but I can…
-
Auto-populate a value in the cell of a column, based on previous values
Hi, Appreciate your help! I have a sheet, where Auto-number is taken up for the first column. I have a Priority Column, which I want to auto-populate based on if the number already exists. Item # | Item Requested | Priority 21 | Test 1 | 1 23 | Test 2 | 2 24 | Test | 3 25 | Test | ---here the number should auto-populate as…
-
Move Row to Another Sheet Once Everything is Filled In
Hi Community, I am trying to make a work flow that moves a row when the Latest Claim Status is Denied. However, I only want the row to move once all the columns are filled in for that row. I don't want any specific cell to be the trigger as the information might not be filled out in column order and I don't want the row to…