-
RAG Formula & Date Formula
Hi I have 3 questions that I just cannot figure out! 1). I want to auto set my RAG based on a score - I've checked the other community questions and have a formula (shown below) based on previous answers but I'm getting a #UNPARESABLE error 2). I want to set a boolean based on whether a date is more than today +7 days in…
-
HLookup work around
I'm brand new to Smartsheet and trying to figure out the best (most elegant) solution to get the result illustrated in the screenshot below. Any help/suggestions would be most appreciated. TIA
-
Use DISTINCT with COUNTIFS
Hey Guys! I am so excited to see that Smartsheet has finally introduced a formula that will compute DISTINCT(Unique) values!! So, I am trying ot develop a formula to calculate DISTINCT count using one column but with a few condition(s) i.e. IFS i.e. [Range1,Criteria1, Range2,Criteria2] Refer below a sample of that…
-
IF AND with Dates
Help! I've searched and I am stuck! I'm new to SS and I cannot get any formula to work. Here's what I'm trying to do; If the status is "In Process" and today's date is greater than 7 days from the due date = Green If the status is "In Process" and today's date is within 7 days of the due date = yellow If the status is "In…
-
Index Match Showing Incorrect Amounts - Formula when there is no information to pull
Hello! I am stuck on the INDEX MATCH pulling incorrect amounts when I reference another sheet. It will work for some cells, but not others. I am thinking since the parent sheet doesn't have all the line item costs for each store such as "Shelving" or "IT/LP", that could be the issue, but not all stores will have those…
-
IF Formula Help
Hi guys, I would like to implement the following automation process of "% Complete" -> "Status". The approach would be: IF: % Complete: 0% -> "Not Started" IF: % Complete: 0% ≤x≤ 100% -> "In Progress" IF: % Complete: 100% -> "Completed" My current IF formula does not display and consider the last 2 conditions, I honestly…
-
Count if date is in the last six months
I'm trying to refine my metrics for the number of Packages archived in the last year. This formula (which is working correctly across two sheets)... =COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP") + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP")…
-
How can i manually enter range values in a formula
I would like to manually enter a list of values in the range parameter for an INDEX formula, as follows: = INDEX (range , row/col number ) = INDEX ( {"Jan";"Feb";"Mar"}, 2 ) = "Feb" In this example, I don't want to have a list of month abbreviations somewhere else on the sheet, or as an external reference to another sheet.…
-
Reference Formulas in Templates
Hello, I have created two templates from sheets that reference each other in a SUM+DATE formula. I would like the new sheets created by the template to reference each other, but at this moment they reference the old sheet from which the template came and need to be changed manually. Any way to achieve this? Thanks in…
-
INDEX/MATCH formula has me stumped
I am trying to understand a formula that someone else has written and I cannot get it. It works great but I do not know how and I would like to. It looks like this: =COUNTIFS(INDEX({EDB Full Range}, 0, MATCH([Equipment / Limit Required]@row, {EDB Full Range}, 0)), >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate},…