-
Sheet Summary CountIFS + Date Range
Hi All, Need some assistance. Found a great post & this on the subject but was not able to get my formulas to work. Hoping the group can help. I have 1 working the other is not. Background: Form was converted over from Google Form/Sheets & several formatting changes had to be made in August. (Probably issue #1) Co-workers…
-
Auto-populating a range of columns with a number based on dates in 2 other columns.
Hi, I am having difficulty figuring out how to auto-populate a range of columns based on dates from 2 other columns. For example.. A contract's start date is Jan 2022 and its end date it October 2022. The value of the contract is 10,000 USD. I would like to populate columns Jan 2022 through October 2022 with 1,000 USD in…
-
SUMIF row is in the next 5 working days
Hello, I am trying to create a formula that counts the total size (EQA) of the jobs that are due in the next 5 working days. I am using this formula but it's coming up with 0 =SUMIFS(EQA:EQA, [Delivery Date]:[Delivery Date], ISDATE(1), [Delivery Date]:[Delivery Date], NETWORKDAYS(TODAY(), TODAY(7), 0)) Any help on this…
-
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…