-
Using LARGE with duplicate results?
Looking to find, for example, the 10 largest projects in our system by allocated resource hours. But what happens when there are duplicates/projects with the same number of allocated resource hours? =LARGE(({total hours}), n) From the result of that formula, in another cell, I then collect that respective project's name…
-
Multiple IF Statments
Hi Smartsheet Community, I have a question regarding multiple IF statements. What I am trying to accomplish: I am trying to put text in a box if the days are greater than or equal to the number of days since completion 0-30 Days, 31-60 days, 61-90 Days, 91-120 Days, 121-364 Days, 365+ Days. Current Formula: =IF([Total AR…
-
Return a value if a cell date is between 2 dates
Hi, I want to automate one of my sheet summary by retrieving the Sprint# based on today's date. (When today's date is between the beginning and end of a sprint, I want the sprint# to be retrieved in the Automated Sprint field of the summary sheet). I have tried to use =INDEX([Sprint#]:[Sprint#], MATCH([Sprint start…
-
Using Sumifs and Month referencing another sheet
Hello! I am trying to sum the values occuring in a particular month referencing another sheet. Here's my formula: =SUMIFS({Summary Roll-Up - Machine Accepted}, {Summary Roll-Up - Machine Accepted}, >0, {Summary Roll-Up - Delivery End}, MONTH(@cell) = 1) The first part of the formula is working correctly, but when I add the…
-
Metadata sheet pulling data from 40 project plans into 1 dashboard?
I am working with sheets, already created. There's 1 project plan and several sheet i.e. a RAID log and a Decision log that go along with each project. Then there is a metadata sheet (1 per project) that pulls info from the project plan and calculates it and then it gets displayed in a dashboard. The lastest ask is a…
-
How do I account for a specific status in a formula calculating expiration status?
I am using the following formula to count the number of contracts that are expired =COUNTIF([Contract Expiration Date]:[Contract Expiration Date], <TODAY()) I want to exclude any contracts that have "Archived" (Value) as the "Current Status" (Column) from this count.
-
Compiling Inventory Data Living Across Multiple Columns
Hi everyone! I am in the process of creating an inventory tracking sheet. Long story short, i built a form to keep track of inventory being taken out, and it offers 5 chances (so they drop into 5 different columns) to track any products counts taken out. I would like to be able to take those 5 numbers and compile them into…
-
Random Updates - Cell History
Hi, we've been using a format where once a form is submitted, a row is copied to another "master" sheet that the team can manage off of. Recently, I've made some changes to the process (around when the copying happens), but what we're seeing is that in our Master sheet, there are random updates to cells (we have alerts set…
-
Formula cross referencing sheet via template
Hello! I have a workflow created using Zapier so that when a new row is added to my master project sheet via a form entry it will update the first row in a template sheet and create a new project sheet from that sample sheet. This all works great with the exception I have one formula that uses a vlookup to tell the status…
-
CountIF, AND, OR, & NOT Statements -- Which Order?
Hi! Was hoping someone might be able to provide some insight here. I am trying to work out a status formula where a percentage is calculated based on row values that have text in them. This would also be dependent on different status within the same row. Here's what I've worked out so far. =COUNTIF([GMB URL]@row:[GMB…