-
Obtain UPPER(LEFT with multiple FIND options
I'm very new to smartsheets and to function formulas. I have a list of server names and I'm trying to obtain the location from our naming standard. I got it to work for all servers that are Production, "pl" but now I have staging servers "sl". Example server names in my Hostname column: l1plservername nap2plservername…
-
Count dates within a specific range
I am collecting performance data for my project. I have a report that contains all of the data I want to analyze and report statistics on. I'd like to look through a column containing dates including some empty cells, and count how many cells contain dates within a specific range. I'd like to count cells containing dates…
-
Limit return value
Is there a way to limit a number returned in a formula? I have a simple formula that returns a percentage (for a completion rate), but I don't want it to show over 100% or it will mess up my averages when used elsewhere. So even if someone over performs, their completion rate should only still show 100%
-
COUNTIFS using an OR with a column hosting 2 variables
Hello, Trying to set this formula to count 5 items using vlookup. The issue I'm having with that of the TYPE column hosting 2 distinct variables. I am not well versed but believe this needs an OR option. Curious if someone might know the best approach here. =COUNTIFS({HUB ERP - Function}, "Data", {HUB ERP - Status}, "In…
-
How to find 2 values from a multi-select column for reporting
I am trying to count how many times in a smartsheet 2 values show up in a multi-select column. example: i have a multi-select column with multiple options and I want to count how many times "Drainage" and "Roadway" are both selected. This can be done with the filter in the grid using the "has all of" options. This is the…
-
Formula to Change Harvey Ball Color Based off of a Word
I've seen so many examples of "Nesting IF" formulas for changing a Harvey Ball to a different color based off of multiple numeric or date scenarios. What I haven't come across is a way to change the ball color based off of verbiage: Example: I change a cell to Rejected and the ball turns Red. I change the same cell to…
-
How do I get my excel formulas to work in smartsheet. I am getting #blocked & #unparseable
-
Invalid Operation with Countifs formula
Hi, The formula =COUNTIFS({CR Requests Inventory Request Monday}, [Monday of the week]@row, {CR Requests Inventory Status}, NOT("5) On Hold")) returns an Invalid Operation error. I checked the column properties and the references, all look good. The same formula works referencing another sheet. =COUNTIFS({CR Archived…
-
Average Days to complete within a date range
I would kindly like to ask for some assistance. I am trying to get the average number of "Days to Complete" if the "Resolved Date" is within March. I've tried the formula below but I am getting an #UNPARSABLE error. =AVG(COLLECT({2021 Archive Days to Complete Range},[Days to Complete]:[Days to Complete], {2021 Archive…
-
Is there a way to limit the max number of characters in a text/number field?
I have a form that I want to use to capture telephone info. Is there a way to limit the max number of characters that can be input in a cell and perhaps show an error message to the user? Also, is there a way to limit input to numerical text only? Thanks. Bill