-
do not run formula if cell is blank
Hello, I am a new Smartsheet user. I have search some previous inquiries on this topic and tried the suggestions but I cannot seem to get it right. What do I need to add so the formula will not run if the check-in and check-out dates are blank? =NETDAYS([Check-In]1, [Check-Out]1 - 1) Thank you so much for your help.
-
Combining "IF" formulas with "IFERROR" formulas
I am attempting to return a simple YES or NO if a company I have is in another sheet. I created a match formula in one column and in the adjacent column, I was hoping to do a simple IF statement to return the appropriate results. Due to the companies which did not return a match being populated with a #NO MATCH error, I am…
-
Determine entries in a dropdown from a formula
Does anyone know if it is possible to select each item in a dropdown where you might have one or more entries, and separate each one in a formula to calculate hours * rate? Or a work around if possible. My current formula works fine for one entry as follows with the [Assigned To] column: =SUMIFS({KCPL Hours _#OF HOURS},…
-
Feature Request: JOIN() carriage return separator
I would like to JOIN() to allow the carriage return as a separator. This would allow me to transpose the data. JOIN([Column1]23:[Column12]23,"\n") would take the row and give me a cell with the contents transposed vertically. JOIN([Column1]1:[Column1]22," ") would take the column and give me a cell with the contents…
-
Help using join & collect function with multiple criteria & having the delimiter be a semi-colon and
I've been able to successfully use join and collect function with 1 criteria, but not two and am also curious if I can have the results within a cell space to look like Ctrl+Enter was used after each one to be able to easily read through all of the results. Working formula with 1 criteria: =JOIN(COLLECT({Product Roadmap…
-
Extract LowerCase initials from name in single column
Need to get correct formula/function to execute the following: PREFIX: PKS- Name Column: JOHN DOE ID Column: 00123456 Desired Formula Output: PKS-jd-00123456 Here is what I have currently: ="PKS-" + LOWER(Name204) + "-" + ID204 and the output is: PKS-john doe-00123456 OR ="PKS-" + LOWER(LEFT(Name204) + "-" + ID204) and the…
-
Date Problem
I recently ran into a problem with dates. I did not realize that “mm/dd/yy” hard coded in a date formula would be an issue in a Date Column. But when I tried to use the “mm/dd/yy” populated date for comparison with another date, I kept getting error messages or other strange results. It would be nice if Smartsheet could…
-
Linking & Formulas
I would like the information from the first document labeled template first to auto populate into the second smartsheet labeled template second. I am managing multiple projects at once and have 7 smartsheets to track the data specific to each of those projects. I have 1 master smartsheet which houses meeting information,…
-
Troubleshooting formula and or row
I created a formula, but have problems. The formula is ........ =IF([Status Date]@row >= Today@row - 14, "Red", "Green") ....... The formula work fine in some rows, but not all. I'm puzzled as to why this is the case. Does the master sheet need to be purged for underlying errors of some kind. I can't duplicate the error…
-
DESCENDANTS function
Has anyone used the new DESCENDANTS function? I have read the verbiage that SS has provided and played with it in the Formula Examples template from the solution center. It is definitely working very differently than I expected it to. Does anyone have some insight as to how this is supposed to be working and possibly some…