-
Upcoming Holiday on Dashboard
Hello All, I like to display the upcoming public holidays on my dashboard a week before so I created a formula to display the upcoming holiday for the Smartsheet dashboard a week before the holiday but I am getting an error #INCORRECT ARGUMENT Can anyone please suggest where I am making a mistake?
-
Creating and IFERROR/INDEX/MATCH Formula for Exact Match
Greetings! I am seeking assistance to create an IFERROR/INDEX/MATCH formula that will look for an EXACT match of an Account ID between two Smartsheet. In Smartsheet 2 below I have an Iferror/Index/Match formula in the Status column that looks at the Account ID @row, searches the Account ID column in Smartsheet 2 and…
-
Countifs for dates that are in the past
I would like to use the countifs formula for each person on my team that looks up their name, and if the action is open, it looks at the due date and if they are beyond that due date. Then have it count the number of occurrences. This is what I tried which got an error: =COUNTIFS({CAR Log Range 7}, HAS(@cell, "Jerry"),…
-
Automated updates to Dropdown List
Users need a way to dynamically match / update a Dropdown list column based on an Inventory sheet, e.g. criteria from another column or sheet. Use case 1: When an item is out of stock, on an inventory list, remove the item from the order sheet and user form. Use case 2: When a project or task is listed as complete, remove…
-
How to return a blank entry if column contains no data
Hi! I'm new(ish) to Smartsheet and am struggling with what I believe is a simple formula. I have a simple rating column in an existing sheet with a 1-5 ranking (1 being low, 5 being high). If the rating is 1, I want my "re-hire" column to spit back a "NO" entry. If it's greater than 1, I wanted it to spit back a "YES"…
-
Need a way to show the top 5 reoccurring values in a sheet
I keep a spreadsheet of all the books I own (dorky I know) and I want to see the top 5 most reoccurring Authors. I have a database and then a metric sheet I use to pull all my data and translate it on to a dashboard. I have over 900 books and I'd like for the dashboard to stay updated as I go. So here's what the database…
-
Calculate task health with status, completion %, and duration
Hi wondering if anyone could help shed some light on this formula. I'm trying to calculate task health taking into consideration of the status, completion %, and duration. Found a formula in one of the discussion that suits, however, when the duration is 0 as a milestone, it came back with divide by zero error. I tried to…
-
columns formula
Hi, I need a formula to select all the cells in a column for exemple like this but excludind the first 5 rows! =COUNT([SEDE RICHIESTA]:[SEDE RICHIESTA]) It works in this way BUT i need no limit (1000) at the end. =COUNT([SEDE RICHIESTA]5:[SEDE RICHIESTA]1000) Is possible? Can someone hep me? Thanks Umberto
-
Planned Value Formula
Hello, I'm trying to make this formula work: =IFERROR(([Actual End Date]100), "(TODAY() - [Planned Start Date]100) / ([Planned End Date]100 - [Planned Start Date]100) * [Issue Type]13)") It stays blank in the cell after deploying it, which is not what I want. The Planned Start Date and Planned End Date columns don't change…
-
Using INDEX/COLLECT to return multiple values
Looking for the proper way to return multiple values using INDEX/COLLECT. I'm assuming JOIN needs to be in there but can't get it to work! Both without and with JOIN, the formula still produces the first value only. This formula matches a project number from one sheet to another, and is supposed to return ALL of the…