-
using COUNTIFS with contains
Hello, I'm trying to count the number of rows where deployment complete or cancelled is not checked, where Deployment Type is "New Deployment" and Deployment Tier is either "Plus" or "Plus Unlimited." The formula I'm using is getting an Invalid Operation error. =COUNTIFS({Deployment Complete}, "0", {Deployment Queue…
-
Index Match returning blanks when there is a Match?
Hello, I have a nested IFERROR Index/Match that is returning a Market Code based on a Unique ID. Formula looks more complicated than it is: =IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({CA}, MATCH([MPL #]@row, {CA}, 0), 2), INDEX({DE},…
-
Check box in sheet when email address entered in a form matches an existing email address in a sheet
Hi, I'm trying to get my dashboard on employee training to automatically remove rows from a report when a specific user responds using a form. I have the responses from the form automatically moving to another sheet/report, but then I want that user to be removed from the 'yet to respond' report in the dashboard. My…
-
Health formula for schedule variance
I am trying to determine the health of a task based on schedule variance. If the schedule variance is: '>0.9 green '<0.8 red between 0.8 and 0.9 yellow =IFERROR(AVG(CHILDREN()), IF([Actual Completion Percentage %]@row = 1, "", IF(AND(Variance@row > 1, [Actual Completion Percentage %]@row < 1), 0), IF(AND(Variance@row > 1,…
-
What formula would I use to read a date from another sheet?
I have a sheet that collects dates in a column "CO Internal Target", I want to be able to list that date on another sheet with a formula. It should be able to read the "Studio Code". Both of the columns are on both sheets. The sheet that I am wanting to read from is the "Location Tracker - SS", the sheet that it is going…
-
COUNTIF matching instances, or alternative IF(ISBLANK workaround?
Hello! I'm struggling with syntax and can't figure out why this isn't working. I have two columns [Total] and [Inspected]. I'm using countif in the first row of [Column4] to count the number of instances both columns have matching values, but it isn't giving me the correct count. Eventually, I'd like to build upon it to…
-
Formatting Dates
I am copy and pasting reference data that contains dates as text. How can I link to a field and get it to format it as a date. The data looks like this Tuesday, August 16, 2022, but Smartsheet does not recognize it as a date. I tried this =[OPD Data]24{DATE(2021,5,5)}
-
Want to verify cells are equal, but getting reversed 'true' 'false' values in IF() statement...
I have a large SmartSheet that contains deposit information for my company plus others we do billing for. Since payments come in long after the service date in healthcare, I may get an outlier payment for a company that is no longer active in current year, but need to process. I've created a cell 'All Deposits' that is the…
-
using index/collect to gather data from 2 sheets
Hello - I have several budget sheets that use an "index/collect" formula to grab information from a master data sheet. However, we are now moving some rows from the master data sheet to an archived master data sheet. Once that row is moved, all of the index/collect cells are broken since the original row with the data is…
-
Calculating completion date from multiple turn around time and date assigned
Hi All, I have created a workload management sheet. Within the sheet 1 column is for Turn Around Times (there are multiple) and 1 column is for Date Assigned. I need to calculate the Completion Date from the Turn Around Time and Date Assigned: Date Assigned + Turn around Time = X. What is the best formula to use? Thank…