-
How to remove the unnecessary commas?
I worked out this formula with someone during a ProDesk session but they weren't sure how to remove the commas when they weren't needed so I'm hoping someone else maybe has an idea! The formula: =JOIN([Inspected Employee's Name 1]@row + ", " + [Inspected Employee's Name 2]@row + ", " + [Inspected Employee's Name 3]@row +…
-
What's your Best Error Checking Column Using Formula?
I have discovered multiple ways to create a error checking column that looks in all columns to see if there's a broken formula somewhere. However, all the ways I have found aren't very elegant and don't provide the type of details I want. Example: =IF(ISERROR([First Column in Sheet]@row:[Last Column in Sheet]@row), 1) This…
-
Help with SUMIFS
I am trying to collect the numerical value based on whether or not work was completed between two dates by a specific employee that was assigned to the work, using SUMIFS. =SUMIFS({Numerical Size 1}, {Date Task 1 Complete}, >={Week 1 Start}, {Date Task 1 Complete}, <={Week 1 End}, {Assigned To}, Employee@row) ALL results…
-
How to get my numbers to correctly add up?!
Hello everyone, I am tasked with tracking budget moving forward and would like to be able to track overall budget but also budget for each POs. I would like to have the formula to add the Children's Children's (if that makes sense) and ignore the Total of the PO. This way I can have the total of the PO's in one line but…
-
Smartsheet summary formula
Hi All I am trying to create a smartsheet formula for the Summary field that will return the maximum someone in a Senior Quantity Surveyor role is being paid currently in the company, plus the name of the person. I have the max salary part sorted - =MAX(COLLECT(Salary:Salary, [Job Title]:[Job Title], "Senior Quantity…
-
Formula to count days past a certain date without weekends
Hi! I have a sheet where one column has a submission/end date ("END DATE: CA Exam 4 Part 2") and another column is a "due by" date ("E4 Window Closes"). I have a column that I keep hidden which has that "due by" date ("E4 window closes") to help in the formula I have running: =IF(AND(ISDATE([E4 window closes]@row), [E4…
-
Formula that returns a value equal to every other day
Date formulas are the most challenging for me. The requirement is to provide a visual calendar that will trigger the quality audit team to complete the audits expected for the current business day. The sheet has three frequency options: Daily, every other day, and weekly. I am struggling to determine the formula for every…
-
Calculating Age from DOB Column
Group, I’m calculating people's age on their birthday this year using this formula: =YEAR(TODAY()) - YEAR(DOB@row) It works for most cases, but for some reason, it returns a negative number for people born in the 1940s. See the screenshot for details. Any idea what I'm doing wrong? As always, thank you.
-
Index Match
Hello All, =IFERROR((INDEX((COLLECT({Annabelle Pruned}, {Client Name (Pruning)}, [Client Name]@row, {Annabelle Name}, "Annabelle"),1),0) This formula I've used (with the help of the pro desk) elswhere under the same circumstances as the one above: Anabelle pruned is a Boolean, Annabelle Name is a text box. The error is…
-
Confused on Index Collect, Join
i have a sheet with a column with the number of records matching a code number (CodeFM) from another sheet. I have summarized the number of records for each code number (along with the code description in a separate sheet from the large master sheet containing the data). All is well here. At the bottom of the this second…
