-
Formula Question - If not blank, return cell value
Would like to concatenate several cells and have that formula: =[Product Codes]@row + "-" + [Unique ID]@row + "-" + [Deliverable Code]@row Would like to add a cell only if the cell has content along with a dash preceding. Keeps coming back as unparseable. What am I doing wrong?? =[Product Codes]@row + "-" + [Unique ID]@row…
-
I need help with a formula or direction if a formula isn't the best way to go
In my sheet I have 4 different dates , requested, procurement, legal and council, and I want the status to change when each date is filled. So if status has a date I want the one quarter symbol, procurement the half symbol, legal 3/4 symbol and council full. can someone help?
-
Need Formula Guidance for finding matches between 2 sheets
I am trying to identify a match for a Deal ID between 2 sheets. I need to provide stats on the results. CSRSheet is Sheet 1's Deal ID Column Sheet 2, where Deal ID is, are form submissions I added a checkbox column for the match formula in Sheet 2. I was using this =IF(INDEX({CSRSheet}, MATCH([Deal ID]@row, {CSRSheet}, 0))…
-
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…
