-
Use MATCH to find the last position of a matching value.
In the period column I have a MATCH formula to get the position of the matching value in Current Month. =IFERROR(MATCH([Current Month]@row, June@row:May@row, 0), "") In this case the value returned is 5. How would I get the position of the last matching value, which would return 8? Thank you!
-
Nested IF with calculations - Unparseable Error
Hi! I am trying to clean up a leave tracking Smartsheet and am just having the hardest time getting my nested IF formula to work. I have been through countless articles in Community and still no luck. The goal is the add the correct amount of days, depending on the type of leave, to then return the date at which that type…
-
Conditional IF INDEX MATCH formula
I am trying to make a conditional index match statement where if "Topic A" is selected from a dropdown column called "Tags", the "Technical Lead" column will have Name A populated in it. Otherwise, it will be populated based on this index match: =INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)) Looking at some other…
-
Can you help me simply this formula. I think I can use "And" and "Or', I just dont know how.
=IF(sum( COUNTIFS({ML student status}, "No",{ML status}, "Qualified occupied"), COUNTIFS({ML student status}, "No",{ML status}, "UU Pre-Approved by Compliance"), COUNTIFS({ML student status}, "No",{ML status}, "QV Pre-Approved by Compliance"), COUNTIFS({ML student status}, "No",{ML status}, "Unit is in Rehab"),…
-
Formula Issue
I have a form where I am collecting data on a metric sheet to translate to a dashboard for a group. I have two questions: I am using =COUNTIFS to collect the answer that is considered a violation + the date of the line, and am separating it out by months. When all the answers that are violations are "No", my formula works…
-
Formula for Not In the Last (x) days on a specific column
Looking for a formula or best way to build a report that notifies me of all the rows on a smartsheet where a particular column has not been updated in more than x days? Last Modified (Auto) does not work as it will update if any column is updated. I'm looking to focus in on one particular column.
-
Update TODAY() function automatically improvements
Hello SS Community, I have been using the required automation to update my sheets automatically since I am using the TODAY() function on my rows. The current setup I have is to check an unchecked box and vice versa. Other solutions found here:…
-
How to output FY statements based on Dates
Hey so I'm trying to translate a formula from Excel to smartsheets and I'm struggling to figure out the best way to go about it. I'll provide an example of the data and the formula from excel to give as much detail as I can if anyone would possibly be able to help me. Thank you in advance! I have a column of retirement…
-
Formula to count two different values in the same column
I'm trying to create a summary field formula to count two different values in the same column. The formula seems to work, but it returns a value of "0" which I know is not correct. =COUNTIFS(PMActivity:PMActivity, "303", PMActivity:PMActivity, "307") Hoping someone can point out where I've gone wrong on this one. Thank you…
-
Countif Statement Not counting a criteria
hi there, I have a Countif Statement running for a Dashboard on a column with multiple select. I have 3 criteria I'm isolating (image below) but the Partnership Criteria isn't counting when it should. I've checked the formula and spelling and it's fine- here is the back end Metric Page I'm using for the "Roadmap at a…
