-
Nested If & And error
Hello, I'm relatively new to Smartsheet and am having an issue building a nested if/and function for one of my grids. I have 2 columns ("Effort" & "Impact") and am trying to get 4 unique values based on the numbers - If Effort@row >12.5 and Impact@row<45 show "Kill" If Effort@row>12.5 and Impact@row>45 show "Challenge" If…
-
Setting up an Office Status Indicator
Hello! I am trying to setup a sort of "office status" dashboard for staff. Can anyone guide me on a good way to do this? I am a bit stumped as to how I can get some of the info across. I have one sheet with PTO information: PTO Start (Date) PTO End Date (Date) Currently on PTO (True , False) Name Request Duration (Whole…
-
Formula to set a color of an icon depending on the % brackets
Dear Colleagues, I am trying a formula with dot icons to conditionally set the color if the budget is exceeded by: 0%<10% a dot remains green 10%>15% a dot becomes yellow 15%> a dot becomes red I have tried this formula but my colors are wrong. Please see an example. I suspect that my formula is incorrect because the 0.19%…
-
ADD LAST COMPLETE DATE
I have the following columns that are linked to another smartsheet: Citrix Status - Network Status - PC Status - Phone Status - Completion Date When all columns have the "Complete" status, the Completion Date will register the date of last column that changed to "complete" status. I really appreciate any help! Rob
-
Having overall sheet health based off of parent and child row?
Hi - I know this has been asked and answered similarly, but I am struggling with actually implementing the formulas. I have created a simple sheet that I would like to have a visual R/Y/G/Gr marker at the top of the sheet that is a roll up of the all of the parent rows below. I am basing the formula structure off of this…
-
Nesting ISBLANK within IF(AND) Statements
Hello, I have successfully established a formula link between two date columns (Start & Finish) and my 3 status trackers (Completed, In Progress, Not Started). The formula I have used successfully is: =IF(AND(Start@row > TODAY(), Finish@row > TODAY()), "Not Started", IF(AND(Start@row < TODAY(), Finish@row > TODAY()), "In…
-
How can I stop my Sheet Summary from changing the location of a cell when form data comes in?
I am creating an inventory form that populates data into a sheet on "Top of Sheet". I want to create a summary sheet that reflects how much of an item we need vs what we have entered on the count (via the form). My issue is, whenever a form gets filled out, it also shifts the cell I am trying to reference down. I want it…
-
How to ignore error msgs when calculating the average?
I'm trying to calculate the average of the "planning notified -QSTO generation" column and ignore the invalid data type. I've tried the attached averageif formula but it seems to be not working! getting invalid data type. Highly appreciate your help! Thanks =)
-
Remove negative (- ) sign from a day count
Hello Community! I am trying to write a formula to calculate net work days between estimated and actual completion dates, in this formula l have a condition to show as blank the Design Completion Days cell when there are no estimated/actual design completion dates. The formula is working but is showing the "-" sign, l…
-
Nested IF formula resulting in #INVALID DATA TYPE
I am working on a formula to indicate whether a vendor is approved for work and needed to add another condition and a new status. It is working for the most part, but a few rows are pulling #invaliddatatype and I cannot see how to fix it. What I want the formula to say: If the [Agreement Expiration Date] was last year and…