-
Looking to create an =IF(CONTAINS Function for a formatted Date field
Hello! I'm looking to create an =IF(CONTAINS function to pull the day of the week into a separate column from the formatted date column. For example: I want the "Event Day" column to turn Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, or Sunday depending on what is in the Event Date column. I have created this…
-
formula question
we are using smartsheet to keep track of community park passes. i have a column for name, account #, tag # and pass #. I need to be notified / alerted if an account gets more than one pass. Is there a formula that can check for a duplicate account number when the pass # cell is not blank? In the below picture, I sorted by…
-
Unique Variable for JOIN function
Hi All, I am attempting to use CONTAINS in tandem with JOIN(COLLECT()) to populate courses employees must take based on the care-area in which they work. In the below example, the contents of 14A are also being pulled into 4A since they both contain "4A". Is there a way to search for 4A as an absolute? (Unfortunately, I am…
-
How Can I Connect a string of IF/AND and ISBLANK statements Into a Working Formula?
There is probably a much sexier way to solve this but separately these 3 groups of statements actually work just fine. I just haven't been able to string them together and get Smartsheet to spit out the info for a given input. Would love to employ a shorter, more elegant solution but would be just as happy if someone can…
-
Using the collect function and "Assigned to"
Hi all, I am using the COLLECT function (within a SUM function) to collect all values associated with tasks assigned to a specific person. For example, =SUM(COLLECT({Range of Values I am Collecting}, {Column where I have person assignments}, ="Person's Name")) This isn't working for me... any tips? Thanks!
-
Time function
I need to record hours in HH:MM in one of the columns of my sheet is there a time format or formula I can use so that entry format is HH:MM . I will not be tracking time in and time out, just the total time worked, for instance, if John Doe worked 7hrs and 20mins, I want to be able to enter 07:20, is this possible?
-
Counting Children using OR?
I have this formula: =COUNTIF(ANCESTORS([Schedule Health]@row), <>"Green"). It was used to count the number of children of a parent row under the Health column if health was green. I now wish to edit the formula so that it counts the number of children if health is green or blue. How can I do so? I've looked up OR but I do…
-
COUNTIF with Todays date changes on full sheet load
G'day I'm having an issue when using =COUNTIF with a submitted date. I'm trying to count the number of submissions to another sheet for; Today, the last 7 days, the last 30 days, and so on. The calculation seems to be working, but the number changes very quickly after the page loads. For example, shows the count as 8, then…
-
Date Formula Issue
=DATE((YEAR[Tactical Training Start]@row), (MONTH[Tactical Training Start]@row) + 1, (DAY[Tactical Training Start]@row)) ^^ This is the formula as it sits now. I'm trying to take the date from the Tactical Training Start column for a cell and add 1 month to the date for a different cell. What am I doing wrong?
-
Need Help with formula: Join(Collect
I am trying to look up multiple criteria (VENDOR PO), (SAP LINE#) referenced in another sheet and return (CPSD) if it matches the criteria. current formula below I need to edit the highlighted in yellow so it looks up the vendor name and then matches the (VENDOR PO) and (SAP LINE#) to return the (CPSD) Below is where my…