-
Conditional formatting based off of blank date cell
Hello! I have tried to solve this problem with a lot of experimentation and reading many posts in the Smartsheet Community. Unfortunately, I just can't get it to work. I am trying to change the color of the circle symbol of a status field based off of the value in a date field (Target Date). I want the symbol to turn gray…
-
Collect and HAS Functions
I need to find the average dollar amount across a range only if the fiscal year IS 2018 and a multiselect column HAS a given value =AVG(COLLECT([Funding Amount]:[Funding Amount], Subsector:Subsector, HAS(@cell "Cell Therapy"), [Fiscal Year]:[Fiscal Year], "2019")) Is what I'm trying to do feasible?
-
Formula creation to auto-populate priority symbol
Hello, I'm looking for help with creating a formula. I'm trying to auto-populate the "Project Heath" column (red, yellow, green symbols) based off of my "Provider Start Date" column. If "Provider Start Date" is in 15 days or less from today, the "Project Heath" should be “red” If "Provider Start Date" is 16 days to 34 days…
-
Counting Months Formula
Hello! I have a counting question I need some assistance with. I'm trying to count the number of months between two dates (Date Opened and Date Closed). I currently have this formula: =ROUND(((NETDAYS([Date Opened]@row, [Date Closed]@row) / 365) * 12), 0) It works beautifully, but we've updated our process from counting in…
-
How to automatically copy selective columns to a different sheet once project status changes?
Hi, I'm trying to copy selective columns to a different sheet once the status of the row changes to "Released". The goal is to create a different tracker from that data. (only for released material). Attached you will see a few columns example. I have a bunch in that sheet. Please see attached.
-
I need help creating a formula that pulls information from two columns.
Hello All. I need help creating a formula from two columns on one sheet and pushed to the summary sheet/dashboard with the totals. The first column is a drop down menu for the order status (New, Processing, Billing, Completed, and Cancelled) and the second column contains a check or is unchecked. I am counting the drop…
-
I can't get the PARENT formula to work without a reference
I'd like to use the PARENT formula to retrieve the data from the parent row for a column called Task Name If I use =PARENT([Task Name]) I get #UNPARSEABLE If I use =PARENT([Task Name]3) in row 3 I get the parent data as expected for row 3, and if I drag the formula it auto-updates the reference. However, I then cannot turn…
-
Counting number of contacts in a cell, "Allow Multiple Contacts"
Hello. I'm trying to count the number of people (contacts) who appear in cells in a contact list column, where I allow multiple contacts. Using a COUNT function always returns "1", independent of how many contacts are in a given cell. I'm not looking to return how many times any specific person appears, only to count how…
-
Formula to calculate the number of minutes request for a specific date and review board
I have a sheet that is an agenda submission for teams, and then another with a list of dates that calculates how much time is left for a specific date and review board (Primary Column). I have three review boards on the same date, so I want this sheet to pull only the Meeting Duration (Duration) for topics going to one…
-
Variance on task list
I have a sheet that is a running list of tasks that gets added to weekly. I am looking to figure out how to show how early, or late, a task is completed in relation to the due date. If the task is on time, I want to show 0. I have been trying to use this formula =NETWORKDAYS([Due Date]@row, [Actual Finish]@row) - 1 This…