-
Match Contact column list to Email Address
Has anyone found a practical solution to match a Contact column to an email address? It can't be done in DataMesh and while I've done some searching on the board and help files, I haven't found a method to do this that doesn't require a lot of manual intervention. The problem with Smartsheet Contacts is that people can…
-
Recommended roll-up scoring?
Hello all, I have a few different methods for scoring in Smartsheets but am wondering if there is recommended documentation or formulas, specifically, for scoring a parent status indicator (RYGB ball) based on its children. Of course, this depends on the definition of each status in context, but again I am asking for…
-
Need help with formula that shows both years and months
Hi, I need help with setting up column formulae for the two (yellow highlighted) columns. I have included the sum fields for each of the columns. Please suggest a formula so that one may see both the years and months of the grant in the column/ cell. For example for the given start and end dates, the Original Length of…
-
Count Distinct Values that only appear multiple times
I have a formula that follows this structure =count(distinct(collect(range, crit_range1, crit1, etc))) that allows me to count the number of unique entries that meet a set of criteria. Now I'm trying to modify that formula that allows me to count the number of repeat entries that meet a set of criteria. In the original…
-
Enter hyperlink to cell, if another cell is not blank
Hello, Is it possible to enter a hyperlink to a cell using a formula? I'm looking to have a column check if a cell is blank, enter "No info" if it is blank and if it isn't - enter the hyper link. =IF(ISBLANK([Provider A]@row, "No info", "<a href="https://weather.com/">Weather</a>") Thank you, Melitta
-
Index Function to return value from one column in a sheet to another column in another new sheet
I'm trying to use Index Function to locate column from one sheet to another. The index function returns a cell only and the equation is INDEX({Test Range 2}, 1) (Row 1). INDEX({Test Range 2}, 2) (Row 2),... All I need is to generalize the equation for the entire column INDEX({Test Range 2}, n (entire column)). Is there…
-
Sort by two dates, then rank based on ascending sort
Hello all, I have a sheet with two key column dates. I want to sort by Date1, then sort by Date2 and if a row has both dates, I want to assign it a number (rank it) based on the order it appears in the sort. I've tried: =IF([Date 1]@row <> "", IF([Date 2] <> "", RANKEQ([Date 2], [Date 1]:[Date 1]))) but it seems that the…
-
Add Actual start and completion date based on status column
All, I want to fill date column based on status column. 1) If status is "In Progress" "Actual Date" is filled by today's date. 2) If status is "Completed" then "Completion date" is filled by today date. For now I am using =IF(Status@row = "In Progress", TODAY(), "") same for completion date column. But when change status…
-
Automated workflow trigger not working
I have set up a series of simple workflows to change a cell's value based on data in another cell. If the source cell value is changed to "50%" for example, the destination cell will automatically be updated with the number 5. There are several different source cells being used, some of which are "yes/no", and some with a…
-
Formula to pull all values of a column in Sheet A to Sheet B
Hello, I have a master Smartsheet (sheet A) that contains a list of projects identified by NAME column and it also contains many other columns. Every project in the list will have a unique name. I would like to use a column formula in Sheet B to pull all unique records of names from Sheet A in to Sheet B. My main goal is…