-
Formula to exclude expired years
Hi Hi, I'd like to count how many "current" contracts we have and also how many we have up coming close to expiry (say in the next 60 days) My reference sheet has a column for estimated end date, I just can't find the right formula to exclude dates that fall prior to the current date. And I have no idea how to write the…
-
How to add columns based on user input from another column?
I have a field called Number of Children in my form. The user then inputs the number of children they have. What I am looking to do is have other fields added based on what the user input for the Number of Children field. For example, if the user inputs 4 in the Number of Children field, I want a Name, Age, and Gender…
-
INVALID DATA TYPE error
Hi, I'm trying to count the cells that match these criteria but am getting an invalid data type error. =COUNTIFS(OR({Tier}, "Experiencia", {Tier}, "Experiencia Plus", {Tier}, "Experiencia Plus Unlimited"), {Renewal Month}, 1, {Renewal Year}, 2023) The formula worked successfully without the OR statement: =COUNTIFS({Tier},…
-
Countifs from one sheet with multiple categories
I need to complete a countifs formula for data in one sheet with multiple categories (screenshots of data needed). The columns that need to be counted have different drop-down options. I have to count active status defects the total (New, open, Assigned, Ready for Retest, Assigned to Vendor, Reopened) for each module. The…
-
Help with IF/ Then Formula Based on Number of Rows
Hey Guys, I want to create a formula that will return a statement when a number of certain number of rows is reached. I have a unique cell that has a number let's say 10, and I created a helper column (Row ID) that will simply designate each row by a number. I want to create an automatic statement in another cell that will…
-
Julian Date to Short Date
I am trying to write a formula to convert a Julian date to a short date. This formula isn't getting the job done. Any recommendations would greatly be appreciated. *note - this formula does work in excel Julie Date Column: 21123 Short Date Column (text/number type and date type were tried) =IF(0 + (LEFT([Process Julian…
-
Index Match Function Help
Hi Smartsheet Gurus, I am attempt to pull information from one sheet to another given a set criterion that is present in both. I believe that either a VLOOKUP or INDEX function should suffice but cannot seem to get things to work. A little background: We would like to be able to have employees enter a form with data about…
-
If cell matches string criteria in one column, THEN return value of another column (same row)
Hello all, I have the following problem. In my first Smartsheet under the column "Revised Catalog Release" I want to output a date value if a criterion is meet. Now this is the other sheet I am referencing: In lay man words what I want is: If column "Task Name" = "Packaged Part Catalog Release" then please return the value…
-
Dashboard not updating after Sheet changes
For a number of years, our team has been reliant on a PDF Dashboard to issue to our clients. Recently, we have noticed that after new data is input into a Sheet, the Dashboard no longer reflects these changes automatically. Our system is programmed to work in the following manner. (A three-step process -…
-
Formula for workdays based of category and start date
Hey there. I'm working on building on a sheet for the following: I need a formula that generates and order date based off of category and start date. (ie If its a "Pool" category, and construction date of "2/1/23", I need a formula that populates an order date that is 10 business days before the construction date. There…
