-
Need help getting to 1.year and 2. month, 12 months ago
I am trying to setup a rolling 12 month helper sheet, but can't figure out the formula to see what the year was 12 months ago, what the month was 12 months ago. Thanks for your suggestions.
-
Index(Collect) - Need to pull a list of projects done in 2022
I have a datasheet with a list of closed projects in 2022. I need to pull from that sheet only the 2022 closed projects that occurred 12 months ago from today. I have a helper sheet, that shows 2022 as the year 12 months ago (manually entering that) and the month 12 months ago (also manually entering that). now need to…
-
IF and Date with N/A
Hello, I'm attempting to put to arguments together keep coming up with "Invalid argument". Each of these work individually: =IF((Date@row < TODAY(), 1, 0)) =IF(CONTAINS("NA", Date@row), 1, 0) My goal is to check the "date" cell. If it less than Today, check. Or if it contains "NA" then check. How do I put these together?…
-
HAS() inside COLLECT()
Can you use HAS() inside a collect function (I think I need index(collect or max(collect in my scenario but unsure)? I have a multiselect column. I'd like to collect all rows where a specific value appears within the multiselect column, then return the most recent row id (an auto generated number column in my sheet).
-
Date formula that returns: Friday before the last Tuesday of the month
Hi everyone - I'd like to pick your brain: I've been asked to automate an update request to be sent on the Friday before the last Tuesday of the month. Is there a formula or a way to calculate this date? Thanks in advance for your help!
-
Use Max Collect to find most recent record with value in multiselect columns
I have a sheet with 2 multi-select columns. The value options for the 2 columns are the same, and are listed in a 2nd sheet. In the second sheet, I'd like to write a formula that finds the most recent date where the value is present in one of the two multiselect columns. Once I have that data, I'd like to know which column…
-
How do I create a formula for a Parent's "Assigned to" Inherit from Active Task
Hello! I am using an agile template to plan sprints for my team. I'd like the Parent to take on the ACTIVE subtask 'assigned to' contact. For example, the 'assigned to' column for the Parent would equal the sub-task's 'assigned to' column that is currently active (as indicated by 'stage'). Is this possible? something like…
-
Checked box applies formula to another row
Hi, Is there a way for when a box is checked to have the formula below automatically apply to the Health column? Basically, we only want Critical Path items that are checked for the below to apply to. =IF(Status@row = "Completed", "Blue", IF([Estimated Completion Date]@row >= TODAY(), "Green", IF([Estimated Completion…
-
COUNT Formula for counting number of comments in another sheet
Hi! I am trying to figure out how to write a COUNT formula to count the number of comments in another sheet by using a cross-sheet reference. How do you include a comment count in a Smartsheet formula?
-
Creating an At Risk Formula
Hello Community, I am trying to create a formula that will create a flag if the tasks are not marked as Completed in the check box column and the Due Date is within the next 3 days. I've made several attempts at using an IF/AND formula, but can't seem to get it to work properly. Any assistance you can provide would be…