-
Countifs Question
I have a sheet that has 2 date columns, 1 is the date we scheduled an activity, the 2nd column is the date that it was actually completed. I need to count the number of times these dates are the same broken out by month. COUNTIFS([Date1]:[Date1], [Date2]:[Date2], [Date1]:[Date1], >=DATE(2023, 1, 1), [Date1]:[Date1],…
-
Formula for Status for Upcoming vs In Future
I am trying to auto update the status column based on the Due Date and Date Completed Columns. =IF([Due Date]@row = "Not Enrolled Yet", "Not Required", IF(ISDATE([Date Completed]@row), "Complete", IF(AND([Due Date]@row < TODAY(), [Date Completed]@row = ""), "Overdue", IF(AND([Due Date]@row > TODAY(), [Date Completed]@row =…
-
Locking a cell after running formula or deleting a formula after it has run
Hello all. I have a bit of an issue, I wrote a formula to enter todays date after a checkbox has been checked in a cell. This works great, however if I uncheck the box the date disappears. I need the formula driven date cell to lock or hardcode the date after the formula has run once, automatically. Current formula:…
-
Can you use the HAS function in a COUNTIFS formula?
Based on what I read about HAS Function in the Formula Basics (HAS Function | Smartsheet Learning Center) I should be able to use HAS with COUNTIFS. When I try to use it in a sheet, I receive the error message: The formula cannot be generated because the HAS function is not supported in COUNTIFS. The formula I tried is:…
-
What formula or function can transfer a date from one cell to another?
Hi everybody I'm looking for a formula to automatically transfer a value (i.e. date) from lower-level cells to a higher-level cell (please see exemplification below). What formula (or function) can be used for the cells D2 and F2, or D3/F3 and C7/F7 respectively? Many thanks in advance for any help.
-
Cross-Reference, confused...
on sheet 1 "TCP Submissions" I am given a priority. This priority needs to look at the current TCP Review Board Date and the second sheet to calculate the date the priority is due. It needs to look at the Analysis Priority on the TCP Submissions sheet (1-5), then match the value AnalysisPriorityTypeDropDown that factors in…
-
cross sheet cell population/formula
Hello, I'm trying to build a formula that references two sheets, where if the cell @ row is the same name in one sheet (column Study Name) and the 2nd sheet, then the contacts from sheet 2 populate. Here is my current formula, which is saying unparaseable. Any thoughts? =IF ([Study Name]@row = {Greenphire Study Intake &…
-
Index match formula
=INDEX({MASTER - Customer Invoice Log - Status}, MATCH({MASTER - Customer Invoice Log Profit Center}, [Profit Center]@row, {MASTER - Customer Invoice Log Inv Year}, [Invoice YEAR]@row, {MASTER - Customer Invoice Log Inv Month}, [Invoice MONTH]@row, 0)) Can someone tell what Im doing wrong. The column im trying to match is…
-
Matching Company Name Based on Member Name Chosen
Hi There! We have sheets called Meeting Trackers that we log the name, company name, and company category of the attendees of each workgroup meeting. We have 30+ meeting trackers that we've maintain between 5 meeting facilitators. Through Power BI we collect all the Meeting Tracker information and it should give us what…
-
CountIFS w/Three Sheet References
I am trying to get a CountIFS formula to work that includes three sheet references and I get errors no matter what I do. I am trying to count the number of tasks with the isParent checkbox not checked, the Status equaling a specific value, and the Category also equaling a specific value. The values are represented in the…
