-
Find Unique cell in a column.
We are trying to move some Excel sheets into Smartsheet! In Excel, we have an address list in a sheet named “Main” and another sheet named “Street“. We extract the unique street names from “Main” and list them in “Street” and we count the number of duplicates of the streets name. My little sample has 7 addresses – the…
-
AVERAGEIF and Quarters
Hello, I have two sheets: Sheet 1 - contains a column with "PROD Release Dates" and a 2nd column that contains a formula to calculate the "Cycle Time Months" it took to complete the work. Sheet 2 - contains 4 rows to show the Quarter start/end dates for 2023. You will see this is referenced as "[Quarter Date End]@row" in…
-
Permutation Generator
Hey Community - I couldn't score enough time at the formulas booth this week to pick someone's brain on this one. I am looking to re-create an Excel tool that I use to create all permutations from a two column matrix, for example, if I have two columns called SIZE and SHAPE, the next column should be a column formula…
-
IF and ISDATE Formula
I want to calculate NETDAYS from Submission Date to Todays Date, or to Comments Log Received Date I have this formula that works. =IFERROR(IF([Comments Log Received]@row = "", NETDAYS([Date Submitted to SJTA]@row, TODAY()), NETDAYS([Date Submitted to SJTA]@row, [Comments Log Received]@row)), "") However, I need to expand…
-
How do we create CASE statements in Smartsheet by Multiple IF statements?
I'm having an UNPARSEABLE error when trying to createa a formula to get a "30 Days" category from a field which used to be "free-form" and recently just became a dropdown. Hence I get "30 Days" or "30 days" in the field. Below is the formula, what is causing the error? =IF(CONTAINS("30 Days",[Business Retention],1),"30…
-
Why is this #incorrect argument set
=SUMIF([Task Status]:[Task Status], "Completed", [Contact(s)]:[Contact(s)], " ricky torres ") I'm trying to show the completed tasks in the contacts column, and specifically for the contacts individually.
-
Ignore blanks in formula that averages the color balls on the children tasks
Hi - I have a formula that seems to work to average the color balls in the children tasks =IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), "Red", "Yellow")) When…
-
Updates to a sheet via form
I've seen several posts about updates to a sheet via a form or request an update via trigger. Here is my task... Create a sheet to track mileage for a list of team member for reimbursement. Information on the sheet needs to stay confidential. I need to update three different cells on a team members record on the sheet.…
-
Average Ticket Open Time Excluding On Hold Status
Hi There, I am trying to report average ticket open time, but we do not want anything with a status of On Hold factored into the calculation. This is my current formula. Open Time is a helper field that subtracts today from date submitted. =ROUND(AVG(COLLECT([Open Time]:[Open Time], [Open Time]:[Open Time], @cell <> "")),…
-
How can I share a report without sharing the sheet.
**Translated via Google Translate** Hello, good morning everyone. On a sheet there is the information of all the sales, I need to share information with each client exclusively about their purchase, it is very practical to generate reports with said information, however when sharing it, the sheet must also be shared, which…
