-
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…
-
Actual vs target Date
Hi, Need a help if my Start Date is 05/24/2021 and end date is 07/01/2021 and in my project plan sheet say i have a task that start on 06/24/2021 and end on 06/28/2021 .However, that task is already been completed and the actual % Complete has been updated to 60% for that task i want the schedule Health should be turn to…