-
How can I create formula to only calculate a set if another column is NOT N/A?
A form was created to collect a scoring of 1-3 for 10 different items. The goal is to collect the values (SUM) and divide by the highest total score (of 30) to get an overall adherence score percentage. This was originally achieved and work by using a helper column to SUM all 10 values into the Score Total column using the…
-
Formula to Trim Cell From Right by Special Character
Hi, I found this elsewhere on the forum and it works 100% as expected but as soon as I change out the special character to what I need it to be, I get Unparsable errors. The formula that works is: =RIGHT([Area Path]@row, FIND(">", [Area Path]@row, 1) - 1) with the desired outcome: But as soon as I change it to the…
-
Index/Match Results Return only matching first entry on list
I"m using Index match to return a contact based on the department entered in another cell. The department entries are copied from the drop down list in the other sheet so they should be exact matches. For some reason it's only returning a value for the first entry on the list, but the others give a "no match" error. This…
-
Move row to another sheet automation dropping contact
I am using automation to move completed tasks to another sheet; however, the assigned contact is not transferring to the new sheet. I have confirmed the format is the same in both sheets. Both are set up as contact columns. Has anyone experienced this and found a solution?
-
COUNTIFS Using And as well as Or
Hello everyone, I am looking to have an information sheet that is looking for several criteria with "and" and "or". For example, I want the formula to count if the other sheet has "Priority 1" and "John Doe" and a status of either 1 or 2 or 3. I will have another cell on the information sheet for "Priority 2" and "John…
-
Challenges with Summary Data Formulas/Dashboard Sometimes working
I have a first sheet that was filled out by people using a smartsheet form to populate a sheet for a certification program. A second program is only open to the people who were already certified in the first program, so they fill out a Smartsheet form with minimal fields, include a unique ID number, and the rest of their…
-
Parent/Children Formula
I am trying to have a formula look at a "Parent" row Status IF it is blank than to then look at the child row associated and if the Status is Red, Green or Blue, place status within Parent Status ONLY, if it is not any of the listed status leave blank. I have the below formula, but it keeps throwing a #circular ref error.…
-
Formula to show certain selections of a drop down selection.
I have a sheet with a drop down list (Countries ) and the user will select countries across the world. And I want SS to return only the selections that met criteria. In the image, the user would select items in the country column and the formula would return items from the Europe Countries Column. I feel like this should…
-
Get data from sheet that contains a multible select drop down
I have sheet 1 (S1), where I have a multible drop-down list with model-numbers + a Supplier 1-column. Here I have the data I need in Sheet 2 (S2). In sheet 2 I have a row with model numbers, WHEN these matches with model-numbers in S1, I need the supplier-name in the row from S1 to go into the supplier-column in S2. This…
-
Collect a contact list from another sheet
Hi Guys Just a quick one, I have a contact list see pic 1 below….. I want a Formula that collects all the Responsible person (Contact) and pastes them all in one cell on another sheet - Recon Monthly Intake sheet, (see pic 2) I want the collected list to appear in a cell on a Column named recon preparer (see pic 2) Can you…
