-
Looking for help - using index/collect potentials
I am working on a complex workspace/sheets for a sales team with commissions and quotas. A while back, I was able to get a index/collect formula to pull in the correct tier for a quota plan: =INDEX(COLLECT({Quota - Lauren 2021}, {Quota - Lauren Minimum$ 2021}, <=[Commission MRR]@row, {Quota - Lauren Maximum$ 2021},…
-
Is it Possible to create Multi-Line Formulas like in Excel?
I've always used Alt+Enter in the MS Excel Formula Bar to break up more complex formulas into more manageable pieces (screenshot example). Is there any way to do this in Smartsheet? If not, this would be an extremely helpful improvement.
-
Looking for a way to capture the turnaround time for new requests
I have a scenario where we have an SLA of 24 hours. I can capture the submission time of requests with the Auto-Numbering/System Generated (Created Date). That gives me the exact Date/Time Stamp of the submission. Now the clock is ticking. However, on the backend is where I'm struggling. I tried the Record a Date…
-
What is wrong with this formula?
Hi, I'm trying to count all consultation meetings between 1/1/19-12/31/21. This is the formula I came up with and it does not work. =COUNTIFS([RPP Consultation Meeting?]:[RPP Consultation Meeting?], HAS(@cell, "TRUE"), [Intake Date]:[Intake Date] >= DATE(2019, 1, 1), [Intake Date]:[Intake Date] <= DATE(2021, 12, 31)) Any…
-
LookUp and Match a value located in a cell with multiple values and return single and/or multiple va
Hi, I am hoping someone can help with this formula. I was able to get this working using VLOOKUP when there were fewer complexities. In Sheet 2 when there is a value in the "Lot Number" cell, * The formula should look at Sheet 1 to see if the "Lot Number" matches and exists in any of the cells located under the Lot Number…
-
CHANGE ON SITE AFTER IS CONFIRMED
I have a column “Equipment Order Status” that has a dropdown (Ordered, On Site, Shipped, Delivered) and another column “Equipment Confirmed Arrived” that has a dropdown (Not Confirmed, Confirmed). I would like when I choose from Equipment Confirmed Arrived” column the option “Confirmed” the column “Equipment Order Status”…
-
Formula Help - multiple criteria & ranges
Hi community! I need HELP. I have been hitting my head against a wall trying to write a formula to calculate across different ranges and criteria. See photo for example of data. My formulas are not working out. What I am trying to calculate is: Countifs 1) All PEM except PAC Support Center 2) Do not include rows with…
-
Resource Management workaround for Rooms (not people)
Because Legacy Resource Management can't handle anything that isn't a registered user, I'm trying to create my own Room availability sheet. I've got columns with week numbers. And my formula is successfully looking at a start date, or end date, but can't identify the times in between.…
-
IF/OR/NOT formula: What am I doing wrong?
There are three criteria that programs must meet before we can release the hold on their reservations: 1) Covid safety plan in place 2) Approval to bring minors to campus 3) Housing plans in place Here is the formula I'm working on, which is returning an "unparseable" error message: =IF(OR(NOT([COVID Safety Plan]@row =…
-
Countifs with contains
Hello everyone, I am unable to write a formula including COUNTIFS and CONTAINS. My formula looks like this: =COUNTIFS({Range 1}; Test1; {Range 2}; "Test 2"; {Range 3}; "Closed (WECO)"; {Range 4}; CONTAINS(City; {Range 4})) I don't know how to use the CONTAINS formula well... In the city column we can find 2 types of data.…