-
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…
-
Looking for help - using index/collect potentials
I am working on a complex workspace/sheets for a sales team with commissions and quotas. A while back, I was able to get a index/collect formula to pull in the correct tier for a quota plan: =INDEX(COLLECT({Quota - Lauren 2021}, {Quota - Lauren Minimum$ 2021}, <=[Commission MRR]@row, {Quota - Lauren Maximum$ 2021},…
-
Is it Possible to create Multi-Line Formulas like in Excel?
I've always used Alt+Enter in the MS Excel Formula Bar to break up more complex formulas into more manageable pieces (screenshot example). Is there any way to do this in Smartsheet? If not, this would be an extremely helpful improvement.