-
DATE function for drop down menus
Hi all, I am using a drop down including different years (2019, 2020, 2021, etc.) that I need for the card view. Based on the selected drop down value in Column 1 (e.g. 2020) I want to auto-populate a ‘date’ field with the first day of the year (e.g. 2020.01.01) in column 2. I tried to use the following: = DATE ([Column…
-
Another automate RYG balls for parent based on child
Hi, I have a project plan with multiple levels of children. Currently, the RYG automation is as follows: =IF(Status@row = "Done", "Gray", IF([Due Date]@row > TODAY(+7), "Green", IF(AND([Due Date]@row >= TODAY(+3), [Due Date]@row < TODAY(+7)), "Yellow", IF([Due Date]@row < TODAY(+3), "Red")))) What I want to do is create a…
-
Fill sheet based on another sheet (if cells contain value)
Hi Smartsheet community, I am looking for formulas or ways to automatically fill sheet B with the information of certain columns of sheet A for all rows in sheet A that contain a value. In a dynamic way, meaning if a row is added to sheet A it should be also added to sheet B. For example: - Sheet A has 1 column with 10…
-
Countifs project status is neither complete nor cancelled
I'm new to this, and I'm having issues with counting the number of projects where Project Type = X and Project Status does Not equal Complete or Cancelled. I'm fumbling my way through unsuccessfully. This will go on a dashboard where I have other working formulas, but this is out of my expertise. Here's what I have so far.…
-
Using data from one sheet to fill in a cell on another
I need a cell to populate based on a cell in another sheet. If sheet 1 contains the same number as sheet 2 column 1 then print notes from sheet 1 column notes else leave blank. This is what I have so far =IF(HAS({Sheet 1 Column 1}, [sheet 2]1), {Sheet 1 notes}, " "). {Sheet 1 Column 1} Refrences the entire column is sheet…
-
Flag By Issue Status Formlua
This seems so basic, but I cant get this formula to work. I have a flag colum that I want the flag to be 'flagged' if the Issue status column has an issue listed as "Open" This is what I'm using and getting an unparseable =IF(Issue@row = Open, 1,0) Thanks in advance!
-
COUNTIFS and/or IFS help needed
Here is my situation: I have a PMO tracking sheet that is tracking many projects through various statuses. I am putting together a metrics sheet so I can create charts to put on a dashboard. In the metric sheet I am trying to count the number of projects by department where the status is not Complete or Cancelled. I can't…
-
Sheet formula efficiencies
Hi, I have a feeling that I should be using "@cell" or "@row" in a formula to make less calculations. Can anyone shed light on a better formula than the one I have started? Basically trying to determine if a date falls between two other dates through a range (i.e. find out which year/quarter that a specific dates falls…
-
Calender based on a report
I want to run a report that pulls data from several different project schedules and tie them together into a overall calendar. Is there anyway to add formatting to a report or to have a sheet mirror the information that is run on the port? I need to either A: Be able to format the calendar on the report(I know that reports…
-
Reference main table to populate a new subset table based on conditions
Hi, I created a workspace that contains a list of project folders (and its respective sheets, reports and dashboards). I'm looking to produce a new consolidated Master sheet based on multiple criteria from each main reference sheet found in the workspace. In the simplified example attached, the new master table must be…
