-
CountIf Formula Help across sheets
I have used this formula in previous sheets but seem to be having some difficulties in pulling the correct data in this new sheet. Current Formula: =COUNTIFS({MazdaPortfolioSummary.EnrollmentRecd}, IFERROR(YEAR(@cell ), 0) > 2015, {MazdaPortfolioSummary.Region}, Midwest1) We are setting up control center and only have one…
-
Using If/or/and to change date based on different drop downs and checkmark selections
Hi all, Adding on to an existing formula to automatically add a number of days depending if a column is checked or not. The bone column is either "NA" or various types of bones which populates the final date in the QC column based what is selected. I'm trying to add if the Timeline extension box is checked, it will add the…
-
Filter through date range between two different columns
Hello, I'm trying to use =countifs to add the number of submissions on a sheet. I've been able to populate the data when I search through one range, but I'm trying to search between two different date columns. Example: =COUNTIFS({Planned Completion Date}, AND(@cell >= DATE (2024, 10, 1), @cell <= DATE(2025, 9, 30)),…
-
Hyperlink support in Index/Match/Collect/etc. functions
As other posts for help point out hyperlinks do not transfer when when using functions to look up hyperlink values. Please add support for looking up hyperlink values via formula such that the Display Text and URL transfer to the destination. See details in the links below. The workaround of linking to a cell in another…
-
Check if a moratorium date falls on the range between Start Date and End Date
I have a list of moratorium dates. I want to confirm if the Start to End Date for each task range falls on a moratorium date. I used this formula and it's unparseable. =COUNTIF([Moratorium Date]:[Moratorium Date], >=[Start Date]@row) AND COUNTIF([Moratorium Date]:[Moratorium Date], <=[End Date]@row) When I test the first…
-
Highlight rows that haven't been updated in 60 days yellow, 90 days orange
Hi team - I've been thinking about this for a couple of days and searching the site to get an answer. I've not found an obvious solution so reaching out to see if anyone has done this before. I created a Modified + 60 days column and Modified + 90 days column but there doesn't seem to be a way to trigger cell highlighting…
-
Function() or @argument for "currently logged in user" for use in Sheet formulas
I would like there to be a function() or @argument (e.g., User() or @user, perhaps?) that I could use in Sheet formulas that would evaluate to the "currently logged in user". This would enable more flexible Filters in Sheets that could be supported by Helper columns for evaluating logic against multiple multi-select Person…
-
OFFSET() function
I've not seen any requests for a Smartsheet's equivalent to Excel's OFFSET() function. For how universal the INDEX(MATCH()) solution seems to be, it strikes me as a notable oversight that there isn't any function to achieve a similar effect for selecting a range of cells offset by a column index value. It would greatly…
-
Best practice to apply formula to columns
Hey y'all. I often catch myself stuck on applying a formula to a whole column. Is there a best practice or more effective way to apply formulas?
-
COUNTIFS between dates & criteria.
Greetings Smartsheet Community, I am attempting to cross reference another sheet and Count all the Completed tasks within a certain time frame. So far I am successful counting the dates in the range using the following formula: =COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1)) Next I'd like to add…