-
Help with Average Formula
Hi there - I'd like to ask for a hand to calculate an average formula. This is a cross sheet formula where I want to aggregate an average time duration by Assigned To, by quarter. (I also have instances where my time duration is zero - I think that was causing me a divide by zero error). Metrics sheet/destination: Source…
-
Index/Collect from Descendants
In this sheet, I have a column [Groups] which is a parent column to multiple "Groups", each of which is a parent column itself to tasks associated with each group. I need help putting a formula in [Start Date] of the parent row that would give me the very 1st value from [Date] column that is a Descendant of [Groups]@row…
-
Too many Cell References Error
So i am making a sheet to keep track of different devices i mangage. While doing this i ended up with a lot of duplicate columns (Ex: Description column and another column named [DESCRIPTION] with the formula "=Description@row") Since getting the too many cell references error i deleted about half of the columns i had and…
-
Issues with Numbering Children (Child 1, Child 2, etc.)
Hi! I am trying to implement a column formula in Smartsheet that assigns an ordinal number to each of the children in a sheet. For example, the first child would get a "1", the second child a "2", and so on. I've tried several options, and the one I liked the most is the following: Create an auxiliary column with a "1"…
-
Adapting an Existing Formula
Hi, I would like to adjust the formula that comes with the Project Management Office Templates. The Smartsheets template comes with this formula in the "Portfolio Metrics" sheets in the Total column and on the In Progress row. =COUNT(COLLECT({Project ID}, {Project Status}, $Label@row)). I would like to change Project ID to…
-
Formula for calculation
Hi Guys, I'm stuck with one critical problem, where I need one formula. I have two sheets Sheet A (contains only applied leaves, not all the dates range) Column 1 - Leave Start Date, eg 01 jan 24 Column 2 - Leave End date, eg 03 jan 24 Column 3 - Employee code, eg ABC001 Sheet B (contains entire range of dates for…
-
Cross-sheet formulas
What formula can I use to look at the data in a cell on another worksheet, then populated the cell with a designated value based on the content? For example, I have a form with multiple choice options such as Excellent, Very good, Fair, Poor, etc…. I need to create a new sheet where the response of "Excellent" is changed…
-
Inspection tracking template - functions
Hello, I am using the Inspection tracking template and it includes the following function: =COUNTIFS({MED1}, "No", {Date}, MONTH(@cell ) = $[Month#]@row) I would like to measure the number of violations per room, rather than per month but I am having difficulty knowing how to change the {Date}, MONTH(@cell ) =…
-
Help to create a "3 Dimensional Reference" Formula
I am trying to create a sum of the same cell in four different Metrics Sheets. I have created the following formula and I get the Unparseable error. Would appreciate help, please.
-
If Match Check Box
How do I write a formula to check a box next to an ID Number if that same ID Number is also present in another column? So Column 1 is ID Number to complete and Column 2 is ID Number completed list. Thanks in advance!
