-
Countif formula suddenly stopped working
Hello, I have a formula that has been working perfectly for the past 8 months. It's a very simple count if formula. Below is the formula that I copied directly from smartsheets. Anyone have an idea why this all of a sudden returned an "invalid opeartion" error when it has been working before? =COUNTIF(Status:Status, "Red")…
-
Formula help - if cell range contain any text, check the checkbox, otherwise uncheck
Hi All, I'm new to Smartsheet and was hoping someone might be able to help. I'm trying to build a formula where if a range of cells with that row contain any text, a checkbox is checked. But if anyone of the cells in that range has not been filled out, the box is not checked.
-
Common Picklist
Is there a way to define a picklist with values and then leverage that sinle picklist on more than one sheet?
-
date formula referencing specific date cell
Hi, I am working in a date column that has the Effective Date: 05/13/2019. I want the Project Kick-off Date to reference the Effective Date - What formula do I enter so that: (i) the Year is always the year listed in the effective date cell, (ii) month is always January, and (iii) Day is always 01? I have tried this…
-
Help getting Index/Match to find search value (number) in a cell with multiple values (numbers)
I have a sheet with the following formula in a column titled "Status": =IF(ISBLANK([PO#]1), "Processing", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = "Loaded", "OTR", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range}, 0)) = "available load", "Logistics Dept", IF(INDEX({Status Range}, MATCH([PO#]1, {PO Range},…
-
Countifs + AND/OR functions
Hi, I'm trying to create a table to display via a dashboard that shows project status in three categories: Complete, in progress, other. Other includes 3 different values that i need to group into that "other" category. How do you smart folks recommend i do this. I've tried a bunch of things but i get invalid #INVALID DATA…
-
Cross sheet formula/reference
Hi everyone, This may be a basic answer, but I am trying to build a dashboard for my company. I have 6 reference sheets that I want to filter into 1 large sheet (the dashboard). The dashboard is an accumulation of weekly data that the 6 sheets feed into. Each sheet has a different employee inputting different data. So, If…
-
Color Auto-Changes
I have my sheet coded so that the status updates as the due date approaches, passes, etc. The only problem is that in a row where the due date is blank it considers it 'Late' and makes it red. I would like for it to just leave the status blank in that row. This is the formula I had used: =IF([Date Due]3 < TODAY(), "Red",…
-
Beware behind the scenes Date Format
Background: A colleague of mine ran into a problem with Dashboards being fed from a Summary sheet which was pulling data from Main sheets via X-Sheet References. The Dashboards were showing incorrect values until the Summary sheet was refreshed (opened) Investigation reveals (I believe) that the issue is due to how…
-
Smartsheet formula syntax help
I am trying to write a formula to add 9 months to date from another cell in my worksheet and am struggling with the formula syntax. Adding days to a date is very straightforward, but I have not been able to figure out how to identify how to add months to a date. Can anyone help with this formula? Thanks, John
