-
What formula should I use to convert drop down text responses to a corresponding number?
We are working on an assessment survey that has text dropdown values. Each of the text responses will be scored with a different numerical value. I have created a corresponding column to capture the score for each of the questions but am having trouble with the formula. For example, the respondent will answer question 1…
-
I want to count past due tasks using 2 date columns.
Hello all, I'm trying to count overdue tasks by using 2 different date columns. I'm currently using this formula , =COUNTIFS([Hardware Actual Construction NTP]:[Hardware Actual Construction NTP], <[Hardware Planned Construction NTP]:[Hardware Planned Construction NTP]) However, whatever dates get put into the columns the…
-
Reverse the order of ancestors
I printed the ancestors of the task and the task itself to generate a trail of the task. However I want to reverse the order of the ancestors in order to start with the task then its parents, the grandparent, and the great grandparents etcetera all the way up until the first ancestor. For example, Paint -> 2. Main House /…
-
Using CONTAINS or LEFT in a COUNTIFS
I am trying to create a helper column to flag when there are not enough safety cones in a vehicle depending on the vehicle type. For example Pickups need 6 minimum, Vans 8min and Buckets 12min. The types vary with PICKUP/FORD, PICKUP/CHEV, Pickup - Service Body, and Pickup. My first thought was to do a contains in the…
-
Modifed By and Modified Date changes in each row when sheet is viewed
Hi all I am having an issue with my "modified by" and "modified date" columns. Sometimes, but only sometimes, when someone views the sheet, it changes all rows in each column to that person and that date, effectively erasing the data beneath. It seems the culprit might be my TODAY formula, which is set up like so;…
-
Calculating Number of Tasks above 50%
I am trying to calculate the number of tasks I have in a sheet, which are currently sitting above 50%... this is for dashboard purposes so we can see how many tasks are in the final-closing out stages etc. My formula is not returning any count whether I try it in sheet summary or a separate sheet.... any clues? Result in…
-
Can I alter this formula to generate a date based off a deadline of the 4th Friday of every month?
As a form submits answers to my Smartsheet, the submission date is recorded under a column titled "Created." This information is submitted to a committee meeting on the 3rd Wednesday of every month, so I needed this formula to specify at which meeting date each response row will be considered. I found the formula below on…
-
Formula to return matching value
Hi! I'm looking for help with a formula that will return the corresponding row value in [Dan Accrual] column when the value of the [Period] column is "Y". I was thinking this should be an index/match, but I can't quite puzzle it out. I'd be thrilled to have this result in the sheet summary, and not need to utilize an…
-
INDEX/MATCH with checkboxes and contacts
Hello, I am working on a sheet that I created that will act like a team member matrix. In the sheet I have two columns that list the name of the project and another column listing the names of who is apart of the project. The other columns in the sheet are all identifiers like (project owner, sponsor, leader, etc.) and are…
-
Count IF formula
Hello, I am trying to find the formula to use to count the number of rows assigned to an email are on a data sheet and will need counts specific for each month. Which I will adjust the formula for all the other columns months. Below I have how to pull for the user email but not sure how to add in the argument for the…