-
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…
-
Is there a way to see how many cell links I have in a grid?
I have some roll up sheets that hold the main data we use in the business. I regularly get the message that I am either approaching or going over the limit for links. I actively manage by clearing links on old completed data. But I was wondering if there is a way to quickly see the number of links there is on the sheet?…