-
Formula / Function to copy cells from one sheet to another
Does Smartsheets have a function or formula that will copy a cell from one sheet to another without the manual copy/paste process? For example, I have a sheet (CDA 1) that will be used to scan a barcode into a cell (Column2 - Row1). I have a second sheet (CDA 2) that I want to auto-populate what was scanned into the cell…
-
Stoplight turn red when equal or greater than due date
Hi, I am a relatively new smartsheet user. I am using this formula to turn a stoplight red, yellow, green based on the proximity of the due date. The yellow and green portion is working as desired, but the Red is not. I want the health to turn “Red” if the due date is = or greater than TODAY. For some reason, it’s only…
-
Count Number of Records in current year
Hello. I am trying to use a COUNTIFS formula to count the number of records in a second sheet that meet two criteria; the Project Status of the record matches the project status of the row where the formula is, and the year of the Move-In Date of the record is the same as the current year. I have the formula in the pic…
-
Doing two things in one IF statement
WHAT I'M TRYING TO DO: I would like to accomplish two things in one IF statement. Something like: =IF([This Column]@row = 1, [That Column]=true ALSO Today(), ....) WHY I'M TRYING TO DO THIS: I am trying to automate the Actual Finish column in my project management sheets such that, when % Complete reaches 100, then check a…
-
Date Formula Problem
See attached. I am having problems with the date date formula. I add 12 months to the moths and the year does not change. The formula is set up to add # of months(MONTHS) to the start date (SDATE) to calculate and populate the end date (EDATE). It seems the formula works so long as the start date and end date are in the…
-
Date Formula
I am trying the add numbers within certain date ranges that are from today’s date, and between 30 to 180 days in the past. For example, see below table DATE NUMBER 10 Feb, 2019 2 14 Jun, 2019 6 15 Oct, 2019 5 28 Nov, 2019 3 I want to add just the 5 and 6, and get 11 as my answer in my Summary Sheet Formulae cell. What is…
-
Assign a value to text
Hi, I'm a newbie. I have an evaluation form where responses are Average, Very Good and Superior. I'd like to have another field in the smartsheet populate upon response with a numeric value. 5 for Average, 10 for Very Good, and 20 for Superior Is it possible to do this using formulas or is there a way to assign a numeric…
-
VLOOKUP for a range of numbers
Hi Community, I am looking to try and do a VLOOKUP against a range of numbers versus a single value. The function as I know it today only allows you to look up a value and pull the corresponding value for it (i.e., If the color is Green, then put 2 or if the color is Red, then put the number 3) In my use case, I am looking…
-
Populate Start and End Dates with Today based on status
My sheet has Start Date, End Date and Status columns My formula in Start Date is =IF(Status@row = "In Progress", TODAY()) My formula in End Date is =IF(Status@row = "Complete", TODAY()) My problem is the date values disappear when the status changes. How can I make the Start and End Date values stick once the field is…
-
Counting values in a multi-select column when "Yes" in another column?
I'm trying to count instances of a specific selection in a multi-select column when "Yes" is in another column. I've tried various FIND, COUNT, COUNTIF, COUNTIFS then IF or AND, but I'm not able to figure this one out. This one ALMOST works, but it won't count when another value is selected in the multi-select:…
