-
Could I refer a cell as part of the sheet name in functions?
I have a column with different carriers such as "AT&T" and "Verizon". And in the following column is a complicated nested functions, and part of it is doing search depending on the carrier name. For example, for the row "AT&T", I want search in the sheet named "ABC AT&T". For "Verizon", I want to search in the sheet named…
-
IF And
Good Day, I need so assistance, I am trying to do the following I want to change my Project stoplight Status Column (RYGB ball) to yellow if the following condition is met if my "# of Days" column is > 138 and my "% Complete" column is < 50% Here is my If condition - =IF(AND(([# of Days]2 > 138, ([% Complete]2 < 50)),…
-
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.
-
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…
