-
Count Number of rows based upon multiple Criteria
Here is my problem: I have 6 columns that may contain a number (score), if they all contain a number (score) they will sum into column 7 for a grand total. In some cases not all of the original 6 columns have a number (score) and I want to count how many rows are missing a score. I already have formulas for count with…
-
Countifs
Hi, I am hoping someone can help me. I am trying to count the number of "dates" in one column, compared to how many "dates" are not entered in another column. So for example, Column A has 15 dates in it and Column B has 5 dates in them. I'm looking to determine out of the 15, how many don't have a date - which would be 10.…
-
Identify Duplicate Values, only within the last 7 days, based on unique identifier (code)
I want to identify when a code is a duplicate in the last 7 days but I'm struggling with the logic/syntax. Essentially people call in for assistance and we track that. When we put a closed date in, automation automatically sends a survey. However, if they call in more than once in a week, I don't want to send them a survey…
-
Formula help
Quarter 2 (April- June) I need the formula that does the following: Take the week of 5-Apr + 12-Apr +Apr-19 and that would populate for the [actual number], the remaining weeks would be [planned]. Than dynamically the formula would auto populate each week consecutively so that it would add the current week to the sum of…
-
Matching data across multiple cells
Hi all, I feel like this should be pretty easy but I can't seem to work out how to get this done... I have a sheet with 3 columns that capture different locations - what I am trying to do is return a status/value if all 3 of those locations match, a different value if none match, and again a different value if only 2…
-
Circular Reference Error Not Appearing Initially, then appears later
I've created a few summary sheets which use VLOOKUPs from my master data sheet, as well as other sheets. I've noticed that when I try to do a VLOOKUP, the formula will initially work, and pull the data as expected. However after some time, I'll re-open the same summary sheet, and see that the VLOOKUP Formula has an error…
-
RAG status formula using TODAY and end date
Hey smartsheet community, any help fixing this RAG status formula? I am trying to automatically show RYGG balls dependant on the end date and today's date. With the exception of cancelled and complete tasks; any task with the end date TODAY or in the future should be green any task up to 3 working days in the past should…
-
How to troubleshoot the error in a formula? I am receiving #UNPARSEABLE error
I am using a simple if statement but receiving a #UNPARSEABLE error for the formula: =IF(([Proposed 2020 Base Salary]@row-[2019 Base Salary]@row)/[2019 Base Salary]@row=-100%,"",([Proposed 2020 Base Salary]@row-[2019 Base Salary]@row)/[2019 Base Salary]@row) How can I troubleshoot this error, there is no option to do a…
-
Using forms to update a record
I've perused the Smartsheet community posts for the answer to this question and get varying results. Objective: Use a Smartsheet Form to update a row (a record) of data. Background: We are currently using an Excel file with a VBScript form to update records of data. The VBScript form allows us to select the record to…
-
Auto Allocating A Person
Hi I maybe asking a bit much here but, here goes..... I want to be able to allocate a person to a task based on the Dept and Quarter No. I have set up a File Review Allocations sheet that shows the number of file reviews required per year by Dept based on whether the reviewer is full time or part time (see last two columns…
