-
Summary Formula
Hello, I am using Smartsheet for project planning. I would like to track adherence to plan, specifically counting the average of the Start to Plan and Finish to Plan and represent the number separately as a percentage so I can see what percentage we start and finish to plan when it happens. I have created an Ancestors…
-
Is there a formula to get values from B1 if A1 is blank?
I want a formula that will return with the value of cell A1 but if cell A1 is blank, then it will look at the value in B1 and Then I want to do an Index match to a different reference. This is what I have so far: =INDEX({Equipment Master Range 3}, MATCH([Asset Number]@row, {Equipment Master Range 4}, 0))
-
Need help with COUNTIFS in sheet summary utilizing 2 columns from the same sheet
I have a sheet with Start Date (date properties), which I have converted to Current Year (text/number properties) in a separate column and then another column called Fully Vaccinated? with dropdown single select properties. In the sheet summary, I have the following formula: =COUNTIFS([Fully Vaccinated?]:[Fully…
-
Counting Unique Values Under Parent Rows
My sheet current has four project types (parent row.) Underneath each project type is the project name (children.) And underneath each project is a list of the team members working on that project (ancestors.) I need to be able to count each unique employee working on projects under each Project Type. So if an employee is…
-
Index/Match formula inconsistent
I have 2 simple index/match formulas to bring over information from one sheet to another. However sometimes the formula returns a #NOMATCH, seemingly dependent on how the data in the data in the other sheet is sorted? There are not parent/child rows so I don't understand why having the rows in a different order would…
-
Partial result
Hello, I'm attempting to create a way to review an account number from one cell, and have a different cell populate just the last four digits with X's for any other remaining characters. So account 1234567 would show XXX4567, where as account 123456789 would show XXXXX6789. Fairly new to complex formulas and not sure where…
-
Formula to include weekends & Holidays in count but exclude weekends as a date for the result
I'm converting a file from Excel to Smartsheets for Quarterly, Semi-Annual, & Annual inspections. I want to set the first inspection date up and then have formulas in place to auto assign the remaining inspection dates for the next 2-3 years. I'm using WORKDAYS with an if formula which is providing a result but it's not…
-
Find "soonest" or closest date in a column of dates.
I'd like to find the earliest or Next available date for 3D Printing Dashboard. I'd like to use IF or IFS given that I would like to search only rows with a "Printing" Status. There are no formulas that seem to help. Any help would be appreciated
-
Trigger a daily automation if a record doesn't exist
Hi there, Our warehouse leads use a smartsheet form to upload a document once a day, around noon. I want to have a notification sent to the manager of a warehouse if that warehouse didn't upload a file for that day. The sheet that the form is built on looks like this: DC is the warehouse number. The formula works for…
-
Nested IF / THEN / SUM Formulas - Based on Values in Drop Down List
Hello! I am working on a Commission log and tracking project. The commission amount is based on the amount billed which is contingent on the contract billing cycle rather than the total cost of the sale. So, I am trying to create a formula that will calculate the billing amount based on the bill cycles from a drop down…