-
Seven Most Current Entries for each Question for Each Location
Hi, I want to pull the SEVEN MOST CURRENT ENTRIES for each QUESTION for each LOCATION. Here's my Master Smartsheet. This is the formula I started using based on responses in the community: =INDEX({TEST Range 2}, MATCH(MAX(COLLECT({TEST Range 4}, {TEST Range 2}, [Q1]@row)), "Clearfield", {TEST Range 5}, 0)) This formula…
-
How do I find the average approval time?
I have a sheet that our PMs approve invoices and another sheet that copies the rows as soon as they are approved. One column is Date Created (Created Date) and the other is Approved Date (Modified Date). When the row is pulled, it captures the modified date when they approve the row. I need to find the difference between…
-
Force upper case on a column
Hello Everyone, I got a question. Is there any way to force upper case on a text column? I have a column where users enter information using iPads and it will be very helpful if the cells will automatically force to have upper case font in it. Thanks
-
need help with formula
I am trying to create a RYG formula Have a drop down column with the choices: 75% staffing 50% staffing 25% staffing <25% staffing I want to say that IF([Staffing Level Impact to MEF]@row = "75% staffing", "Green", IF([Staffing Level Impact to MEF]@row = "50% staffing", "Yellow", IF(OR(([Staffing Level Impact to MEF]@row =…
-
COUNTIFS results in Zero (and NOT for same reason as in rsmith's question)
Why am I getting no errors, but a 0 (expressed as a percent as formatted) when using the following formula? Please note that I am NOT the person who named these columns or the list of values therein, but you've gotta work with what you're given, right? Here's the formula: =COUNTIFS([SF Phone decom'd/removed]2:[SF Phone…
-
Average a column with zeros
Hello, How do you average column and not include the cells with zeros in them. Value 1 Value 1 Value 1 Value 0 Value 1 This column will grow larger and larger as more form data comes in. Thank you
-
formula help
I need to write a formula to count the number of projects in a sheet but subtract all projects that are tagged (checkbox column type) if the cyclical column is checked. Does anyone have any suggestions or help they can share?
-
Problems with formulas
i am having difficulties with my formulas . I am trying to acheive the following: IF status row is not "complete" AND due date is Today or in the past, if true Flag, if not unflag. My formula is: =IF(AND(Status28 <> "Completed", [Planned Finish]28 >= TODAY(-15)), 1) However this is the results - it is not calulation what i…
-
Is there a way to auto-hide columns based on the value selected from a drop down cell?
I have a main grid with several columns and a webform that feeds into that grid. This tracker is used by my team to track several job "types" defined in one of the destination grid columns as "Large Project", Medium Project" and "Small Project". Each distinction has its own requirements, thus making a few of the existing…
-
RGBY Ball and Date Formula
Hello, I am trying to write a formula that can work out the following RED - if the start date has past and the complete % is 0 BLUE - if the start date is in the future and the complete % is 0 YELLOW - if complete % is 1-99 GREEN - if complete % is 100