-
Status Updates
Good afternoon gurus! Here is our question of the week! I have a process that follows a material through different stages of release. This process spans across multiple sheets, some sheets containing multiple steps in the process. I had a "status" column requiring manual updates, but time has shown that automation would be…
-
How to make a COUNTIFS Formula work
Hello, I'm hoping someone might be able to help me make a COUNTIFS formula work. Some background... I've set up a sheet that aims to capture a team's availability of staff in any given month, should their be a major event occur. The staff would complete a form that asks for their name, contact details, job role and their…
-
SUMIF or SUMIFS and how to use
I need to obtain a running total of days for an inspector for a given month in a column “Days Per Inspector”. I have the following Sheets & Columns: Inspector (Personnel) Sheet Days Per Inspector (SUM – Number) Inspector Contact (Name – Contact List) Inspections Sheet Inspector 1 (Name – Contact List) Inspector 2 (Name –…
-
Calculating elapsed time in percentage
I am looking for a formula to help me with elapsed time in % complete based on date columns. This is the formula I have used and the name of columns. In excel we use DATEDIF but I have tried different ways of doing it. It may be I don't need all the columns. Please help. =IF((COUNTIF([Projected Start Date]3:[Step…
-
=NETDAYS formula updating in the afternoon
I am using the formula below to count the number of days since an item was approved, to today. For some reason it updates in the afternoon. I believe the update happens at 3pm cst. It makes the data look unreliable because it feeds a lot of metrics. For example, it feeds a metric for # of items opened in the last 7 days.…
-
Automatically enter certain Text in a cell if Date Open > 60 days
I currently have 2 formulas that work in conjunction to look at Acct Open Date and if it's greater than 60 days it populates a column/cell with yes or no (via checkbox) then, looks to a SECOND column/cell and tells the first cell to enter the text that is in the reference cell. It works fine but it relies on me copying and…
-
Set Parent's Projected Date based on Children's Status and Projected Date
I am using several layers of parents + children rows, and I need the duration / projected date to be "TBD" when a child row is "In Progress" or " Not Started." Here's the current situation - when a child row has a date set, it rolls up to the parent, which is inaccurate as a projected end date since there are still items…
-
Formula to Check How Many Boxes are Checked
I have a metric summary sheet that I am building to use as the point of reference for my dashboard. On my source sheet, I have a check box column for "Copy Needed." I want to count how many people requested copy needed when they made their request. So, ultimately, I need a formula that will pull back the total number of…
-
Conditional Dashboard Formating?
I have a sheet with several rows, and those rows may or may not have sales orders. If the row has a sales order then its booked, if it doesn't then it's pipeline. I'd like to have a dashboard pull data from this sheet that adds up all of the rows that contain an SO and also add up all the rows that don't have an Sales…
-
Separate last name, or auto enter "GENERAL" if cell is blank
My smartsheet is autopopulated by a form in which a submitted can enter "referred by". If a full name is entered, I want to separate the last name and first name to other columns so I can group by last name. I have the formula =MID([REFERRED BY]@row, FIND(" ", [REFERRED BY]@row), 20) to pull out the last name. If there was…