-
Change Status Ball for Old Row Entry When New Entry of Same Name is Created
I have a sheet tracking facility access qualifications for contractors; I need to change the status ball for a previous entry when a new row is created of the same contractor. Basically, when "Ron Cook" has a new row entry, change the status ball on the previous "Ron Cook entry to trigger automation to move the old row to…
-
COUNTIF + CONTAINS - how do I incorporate them both?
I'm trying to count how many times a value is used in a column. Sometimes the values are alone, sometimes there are multiple in a cell. I got my COUNTIF formula down: =COUNTIF({Christoph Comms Tracker Audience Category}, "Select SMG Employees") But I want to change it so it counts cells that include the value (along with…
-
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…
