-
Date formula returning "#INVALID DATA TYPE"
Hi guys, I can't figure out what's happening here, hoping someone can point me in the right direction Importing a table with a "Date" column set as primary column, Date format is YYYY-MM-DD Trying to convert the value into a format I can use by splitting the date string into it's seperate components using MID function, and…
-
Incorrect counting of blank rows
="Completed " + COUNTIF([Completed Tasks]:[Completed Tasks], 1) + " of " + COUNT([Completed Tasks]:[Completed Tasks], IF([Task Name]:[Task Name], "")) I have a checkbox column that will read in the summary bar (row 1) when working "Completed 6 of 33" However, it is counting blank rows and should read "Completed 6 of 25"…
-
Verify All Sites Submitted Response Today
Hi, I'm trying to track if a site (or Name) created a new daily entry using a Smartsheet form. I added this formula to the Updated Today column to return a "Yes" answer if the site (or Name) created a new daily entry. This seems to be working as intended. Is there a better way to design this formula? =IF(Created@row =…
-
Counting physical inventory while removing duplication...
I'm trying to count the number of unique pieces of equipment that are on my worksite. Each unit has a specific Unit Number listed in a sheet. Units that are On-Site are indicated by a green dot in another column. The tricky part for me is, some of these Unit Numbers have multiple work orders which are listed on another…
-
How to ensure data privacy?
Hi I want some of my users to be able to submit data using forms. But in order to be able to fill out a form, the users must have shared access to the sheet that contains the input form in question. With sheet sharing enabled, the users can easily see other users' data as well, which I want to keep confidential! Is there…
-
Which formula should I use if I want to add up all values from a column?
To give a little bit of context, I am creating a report where I can count whether a cell has been filled with information. The column I am using is a drop down list with 5 values. Let's say these values are apples, bananas, oranges, pears, etc. I am not looking for the total of each specific value, I want all of the values…
-
At Risk Formula Unparseable Error
I am trying to build a nested at risk formula based on the following triggers. -End date is today and Status is not complete it will trigger -There is not enough time between today and the end date for the duration period left. So, if duration minus % complete is 4 days. and the period of time between today and the end…
-
How can I compare two cells in a cross sheet reference?
Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beautifully. =COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1,…
-
Different types of zeros?
The formula below is creating multiple "zeros". See pictures. What am I missing? =IF(Balance@row = "ERROR", "ERROR", IF(Balance@row = "TBD", "TBD", IF(Balance@row = "DEDUCT", "DEDUCT", ((SUM(Jan@row:Dec@row) + SUM(CHILDREN(Jan@row:Dec@row))) - (((SUM(CHILDREN([Contract Amount]@row), "Change") + ([Contract Amount]@row)) -…
-
IF(OR with RYGG
I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date is before due date, Yellow or if Finish date is before due date Green, etc. An…