-
Cross Sheet Formula - Count how many times a date appears within a date range
Good morning, We have a log sheet that is constantly building on itself for when we communicate with a client. We call it a "touch." I am trying to count how many times we have had a touch per week of the year. I thought this would be simple, but for some reason I am struggling to make this work for me. Hopefully the…
-
Index Match Formula Errors
This is a two-parter: First, I have a sheet that references a main roster that contains information such as a person's role, % billable hours, and week number. I am successfully collecting data with the following formula: =AVG(COLLECT({% Billable}, {Week#}, Jun$5, {Role}, INDEX({Role}, MATCH($[Primary Column]@row, {Role},…
-
What formula(s) would work best for this task?
Hello Smartsheet community! I have a question regarding what formula(s) would work best for a specific spreadsheet I am building. The situation at hand: I have a spreadsheet with data that has been submitted through a Smartsheet form. This data is connected to a users alias and tracks their completion of training. There…
-
Check to see if two values fall within the same date range
Hi, I am trying to build a schedule tool using a gantt chart as a base. I need to check to see if a piece of equipment is being scheduled within the same date range but I cannot seem to figure out the formula (if this is even possible). E.g. Item 1 has been scheduled for use between 2/9/21 to 2/11/21 but Item 1 has also…
-
Splitting Amount into equal payments across several cells
Hello, I am needing some help trying to figure out a formula that will take an amount due and split it across a determined number of months. I would also like it to return a "due date" based on a "start date". Total Amount: $77,760.00 # of Months: 6 Start Date: 2/1/2021 I would like to have the amount due ($77,760) split…
-
Date Calculations
Hi there, I am trying to do a simple formula for a date to automatically populate in the Date Due column. I take the Event date and subtract the number of days prior to the meeting for each individual due date. This is the formula I created. However, when I run this formula in the Date Due column, nothing is returned. What…
-
Not include value in summarizing information
Hello. I am summarizing information from our SmartSheet based on alerts for both "state" of project and the End date. When summarizing information I want to make sure we are excluding "state" = grey. How do I exclude that in my formula? =COUNTIFS(CAPABILITY:CAPABILITY, CONTAINS("Development", @cell), State:State, "Yellow",…
-
RYG Automation
Would like to automate the RYGB based on % Complete Blue = 100% complete Green = <100% Yellow = 0% past Start Date OR <100% past Finish Date Red = <100% 5 days past Finish Date Already have a symbols column "RYG" Already have "% Complete" column
-
I want to make my RYGG ball gray if the Start Date field is empty (currently showing up as Red).
Here is the current formula. I've spent too much time trying different ways, can someone out there help me? =IF([% Complete]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY() + 7, "Yellow", "Green"))) Thank you!
-
If Formula
Hello. I have a column name Section and a column name District. I have the Section numbers in the column and I need it to calculate the District number. So if my Section Column has any of these Sections 001, 002, 003, 004, 011, 013, 015, 035, 031, 032, 033, or 034, I need it to mark the District column as District 1. And…
