-
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…
-
Index, Match, Collect?
Hello Community, I am hoping someone has a suggestion for my issue. I searched the previously asked questions and couldn't seem to find it. Ultimately, I am trying to maintain one master sheet of data that populates secondary filtered sheets based on their "Area of Expertise". I realize I can do this using the reporting…
-
To SUMIF or SUMIFS - Neither are working
I am a newbie and I'm stumped. I am trying to calculate the remaining budget for projects that have a ProjPortStat(us) as "active" in a field on the Sheet Summary (I can successfully calculate a single column where the status is "active"). I've tried 100's of permutations of SUMIF and SUMIFS and each time is either…
-
How to take Not Applicable and Completed item in consideration when calculating schedules?
Hi, I am working on building construction scheduling. In the template of my schedule, I have a list of items with predecessors. My questions, once I marked one item as N/A or DONE(two separate check boxes), how can I make the following items that have relation to this item to catch up? For example. let's say item A was on…
-
last, first to first last
I want to flip a column which will be last, first names and return first last. So Doe, John becomes John Doe I can get each piece - but is there any functionality to build the string? Having a first name column and a last name column is not helpful. Last Name Formula: =LEFT(NAME31, FIND(",", NAME31) - 1) First Name…