-
Calculated date that doesn't fall on weekend but next week day
I have a date field (Date to be Completed) where I calculate so many days before or after a "Course Launch Date". I need to make sure that if that calculation lands on a weekend it automatically advances to Monday. Here's the steps, just can't figure out the formula. Course Launch Date: (PARENT): 5/26/20 Date to be…
-
NETWORKINGDAYS
I have the following conditions: 1) Planned Ship Date (9-25-19) 2) Actual Ship Date (9-11-19) If the Planned Ship Date = Actual Ship Date, Result should = 0 If Actual Ship Date < Planned Ship Date, Result should be a negative number (This should be -10) If Planned Ship Date>Actual Ship Date, Result should be a positive…
-
Autopopulate text based on drop down option
Hello! I'm trying to figure out a formula to autopopulate text in a cell based on a drop down option in a previous cell. Basically one column would contain the drop down (Campus) and based on which campus is selected, a different cell (account) would autopopulate. Ideally it would look like this: Campus (drop down) Account…
-
Status Formula
Can anyone help me figure out how to identify a project status as "Complete" only if all of the tasks in the entire column are complete, or as "In Progress" if there any tasks that are not complete?
-
How to use result from a multi-select drop down in COUNTIFS formula
I would like to Count the values from a row that contains 1 value from a Multi-Select and 1 of 2 values from a drop down. I have two formulas for the 2 values because 1 of the drop down values contains a < symbol. Here is what I tried to put together The first one is not registering any values. =COUNTIFS([Does customer…
-
Marking Oldest 5 Projects
Hello! I have been working with some data and I want to eventually be able to pull a report of my "past due" projects and show the oldest 5 projects. To do this, I have created a helper column in my source sheet that using the formula: =IF(Date@row < SMALL(Date:Date, 6), 1, 0) in order to flag the 5 oldest dates in my due…
-
How to build bar graph of submissions by user
We are using Smartsheet to build a Change Management process for our IT department. The way it currently works is we have a form that users fill out containing all the details of their change request, which are recording to a spreadsheet. I'm building a metrics dashboard for this, and am trying to determine a way to build…
-
Summing Data From Another Sheet
I have a resource tracker that is set up to sum hours by resource using the =SUM(CHILDREN()) Function. I also need to be able to sum hours by Project Code (Column 4), and Classification (Column 8) by Month. Can someone advise on the best way to approach this? My first thought was to use a SUMIFS formula -- =SUMIFS([Jan…
-
Formulas for flags
I need some help with formulas - cell names are in brackets. If [HRSS Sends Request for Personal Info] has a date, AND [Credentialing Initiated HRSS] is blank after 7 days, flag If [Credentialing Initiated HRSS] has a date, AND [App Completed in Portal by Provider/CVS] is blank after 7 days, flag
-
Skip blank cells with YEAR function
I’m endeavoring a roll up sheet that will provide sum totals from a single row on a separate Planning Request sheet. If the source sheet has all cells filled out, the formula seems to work fine: =SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning…