-
How to avoid counting a drop down option?
I have one drop down option which is used on 3 cells, 2 cells have "EVT" and "OK2Fab" and 1 cell "EVT" "OK2Fab" "OK2Tool", when I use: COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "OK2Fab"), HAS(@cell, "EVT"))) It counts 3 and when I use COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "EVT"),…
-
Formulas for year to date, quarterly, and monthly
Hi, Is there a way to do formulas for year to date, quarterly and monthly? And if so, how can I do that formula?
-
Conditional logic to look up cell entry
Hello, In my task tracking sheet, I have a status column (dropdown with one of the values being completed) and Completion Date column that I need help with. I want to be able to conditionally allow Status field to set to complete only if there is an entry in the completion date in the Completion date column. If the date is…
-
How do I extract first and last name from an email address in proper form?
Hi, I am trying to extract the first and last name from an email address with the names in proper form (first letter capitalized). Is there a formula to do that? Here is an example of what I'm trying to do.
-
Countif two fields from two different sheets match
Hi All, I am working on a data summary table and want to try and make my life easier and not have to edit each cells formula. I would like to take two matching drop downs from two different sheets and if they match, count the rows the fields match, based also on some secondary requirements. My current calculation is…
-
Auto populate a date field when selecting a weekday
When I select Monday from a droplist column named WEEKDAY, I want next Mondays date to auto populate in date column named Date. Same for each day of the week. Is this possible?
-
WEEKNUMBER formula
Hello, I am trying to count the number of weeks between two dates. I have applied this formula =WEEKNUMBER([SD End]@row - WEEKNUMBER([SD Start]@row)) It doesn't seem to be calculating correctly. For example, returning "51" for a SD End date 2/3/23 - SD Start 11/14/22. That answer should be more like 11, right? What am I…
-
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…