-
Formula to convert numerical values in Column A with LOW/MED/HIGH
Hi there, I hope someone with a big brain can help me solve this issue I'm having. Desired Result: I'm trying to fix a formula that will automatically input the Values "LOW", "MED", or "HIGH". I would like to be able to copy the three (corrected) formulas I have into each spreadsheet I need to work with, without having to…
-
checkbox if statement based on date and multiple drop down options
I'm trying to set up a formula that will check an "At Risk" flag based on a date and several drop down options. Example for row 7 : If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag or if the [Delivery Date Estimate] is less than today and the Status does not…
-
Formula Writing-COUNTIFS & SUMIFS
Hello, I am trying to get a couple formulas to work to filter down a roll up sheet. I have tried the following formulas to no avail: =COUNTIFS(Phase 3:47, = "Leads", [ Phase 3:47, = "Closed"], [Phase 3: 47, = "In-Service"], [Phase 3:47, = "Closed"]) =SUMIFS(Total Project Cost(), Phase()="Leads") Let me know if there is…
-
Countifs Multi-select Contact
Hi Everyone, I have a sheet that contains a list of projects. In the sheet I have a column that is setup as a contact column and allows multiple contacts (Project Managers) and a column for project state (State). In my statistic sheet I list all of my project managers(ITPMO ProjectManager) and I want to look back into the…
-
Total cost to previous Wednesday
Hi everyone, I have a question and hope someone can help me with this. If I have a new task & I want to get the total cost until the previous Wednesday, is there any formula that I can use in Smartsheet? For example, if I open the file today, it will show the total from the start date to April 3rd. Thank you.
-
Partial Text Search in Range - Index/Match
I have searched for solutions and getting weary. Challenge find PO# (a 6 digit alpha-numeric) in a string of text (GL_Remark on another sheet to index the actual value cleared general ledger. I had this formula working in Excel: =INDEX(General Ledger_AmtPaid,MATCH("*"&[@[PO/INV]]&"*",GL_Remark,0)) In SmartSheet Only finds…
-
Checkbox If Formula
Wondering if this is possible: I am trying to create an IF formula in a checkbox column where, based on the Start Date of one of the rows, if the date is in the future, it will not be checked, but if the date is today or in the past, it will be checked. I was playing around with the below formula but it was not working.…
-
VLOOKUP is data type of TEXT instead of DATE - sorting is off
I am currently creating an report which looks up dates based on specific milestone tasks over a number of project sheets. When the vlookup calculation is entered and I hit the Return key, is it giving me an alert that the value is not in date format. Being the admin, I am able to override this. But when sorting, it sorts…
-
Return the NETWORKDAYS of 1 month of a 2-month duration
The goal is to count the number of days that an Employee is not assigned work within a given month (MONTH A, MONTH B, etc). For example, a given month has 23 working days; I could use NETWORKDAYS of the duration to count the assigned days of that Task; say 19. To reach my goal, I would simply subract the NETWORKDAYS from…
-
How to Calculate Standard Deviation in a smartsheet?
Hi, I'm working on getting all metrics for a large project into a dashboard in smartsheets. I've been tracking a burn up chart in google sheets, but think that connecting directly to it doesn't look clean. So in an attempt to copy it over, I'm starting from scratch but can't seem to find how to do standard deviation as a…