-
Latest Date Before Today
Hi I'm trying to figure out if there is a formula that will return the latest date from a range as long as it is before today. To explain, I have a sheet that has a list of record of previous and planned visit dates per site. I want to be able to find when the last visit to a site was. I've tried using the MAX function,…
-
Calculating Amount of Requests per Month
Hello, I'm trying to calculate the amount of requests (using the AFID column) that our department gets on a monthly basis so that I can track them on a dashboard graph. For the month of January 2019, I have the formula: =COUNTIFS({AFID}, AND(MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR(@cell) =…
-
Moved: Use "Copy Row" Automation to Generate a New Sheet?
This discussion has been moved.
-
Auto change status to complete based on start/finish date?
Hello, I've looked through the forums but can't seem to find a formula that works. My columns are status (Not Started, In Progress, Completed, and ERROR). I need the status column to reflect what is input into the start/finish date ex: start date today end date blank = In Progress. I also want to display an ERROR message…
-
Nested IF with CONTAINS
Hello, can anyone help me why this formula doesn't work? =IF(CONTAINS("Red",Issue1),"Red"),IF(CONTAINS("Green",Issue1),"Green"),IF(CONTAINS("Yellow",Issue1),"Yellow",)
-
Moved: Dropdown Menu Update
This discussion has been moved.
-
Moved: Dropdown Menu Update
This discussion has been moved.
-
COUNTIFS Syntax
I am really struggling with COUNTIFS syntax and hope someone can shed light on what I am doing wrong. I have 3 sheets. Sheet1 has assets from company a, Sheet2 has assets from company b and Sheet3 is my metrics sheet that has the COUNTIFS code. On Sheet3, I have a COUNTIFS statement that is supposed to count the number of…
-
How do you capture accurate COUNTS by excluding certain criteria?
Here's my use case: I have an Actives Projects smartsheet that captures all data associated with our projects in flight, including Project State and Requesting Team. I created a separate smartsheet so that I capture specific data that I can then use on an exec summary dashboard. In the first column, I have a list of…
-
FIND returns INVALID DATA TYPE
I have a formula to look for a specific quarter in a field Example of data in the field: "Q1;#1;#Q2;#2;#Q3;#3;#Q4;#4" My Formula: =IF(FIND("Q2", JOIN([Estimated Quarter]@row)), 1) If my search string is at the beginning of that field, it returns a 1 just fine, if say "Q2" is anywhere after the first character it returns…