-
Formula to Join Cells that have Content (with Line Break)
Looking to see if there is a formula that would allow me to join cells into separate lines ONLY for cells that have content. Essentially I want to remove blanks somehow so it is just seamless data. In my example, when I use CHAR(10). It joins the cells, but leaves blank lines if nothing was filled in for a cell. Im…
-
Formula =COUNTIFS ... to not include certain text
ok almighty people..... I currently have =COUNTIFS({Depots1}, [Column1]23, {Product1}, FIND("Other", @cell) > 0) This counts cells that depot is = column 1 row 23, if the product column contains the word Other (product column a multiple select cell, with free type option) However I want to count cells that depot is =…
-
Inventory Formula Question
I am trying to keep track of items we have to give out to community members. Mostly I am just making sure I know when I'm getting low on a certain item. I made two sheets, one with the name of each unique item and one with more details that allows me to keep track of the person and date the item was given. Those details…
-
Formula to collect column names if there is a "1" present
I have a pivot table that I am attempting to use in order to collect a list of all jobs that our subcontractors have/are working on. Basically, I want a roll up of all jobs that each sub has worked on. The sheets where this info is held are all on individual sheets, which is the reason I used a pivot table to try and get…
-
Customer Survey and NPS Scoring
I have set up of customer survey that includes an NPS question. I am not too familiar with reporting and dashboards yet, so I am having a difficult time envisioning where I should do my NPS calculations ie. Intake sheet, separate sheet, report, etc. This is also my first "solution" build. Currently, I am putting the…
-
Net Promoter Score Calculation
Hi all, I'm stumped on a formula (again!) I'm looking to ascertain a NPS and I've got the following going on: Promoters: =COUNTIF([How likely are you to recommend NCR to a colleague]:[How likely are you to recommend NCR to a colleague], ">=9") Detractors: =COUNTIF([How likely are you to recommend NCR to a colleague]:[How…
-
Help with Trend Data
I need some help with collecting data for trending. Currently, I have multiple projects with all of their top line data dynamically linked to a Roll-up Sheet. On this roll-up sheet I have data that is calculated from the project Status. Ex: How many projects are Red, Yellow and Green. This works really well for live data.…
-
If(And( formula isn't returning correct colors
My goal is: if NCLEX is greater than 95% = Green If NCLEX is between 85%-94.99% = Blue If NCLEX is between 75-84.99 = Yellow If NCLEX is less than 74.99 = Red Below is the formula I am using and it is only returning Red =IF([NCLEX Passing Rate]@row > 95, "Green", IF(AND([NCLEX Passing Rate]@row > 85, [NCLEX Passing…
-
How to keep a list in order when drawing from a dynamic source
Hi all, I have a list of projects in a status update sheet. This list is automatically updated from a sheet that receives updates from a JIRA to Smartsheet connection. My issue is that the list of projects is in a priority order (with a seperate column to give a priority number), but sometimes when the JIRA pull sheet is…
-
Include Hyperlink in Formula where I'm pulling through the parent task?
Hello! Im my sheet I have a formula in a helper column to pull the value of the parent task into each row - our parent task is our project deliverable name and it helps to have that in line for other views / reports. In our parent tasks, we are hyperlinking to our project brief. However, that hyperlink does not pull…