-
Show corresponding cell based on another formula
I have a current sheet where I want to surface the next major milestone and next major milestone date. Currently there are a list of tasks and dates and a checkbox to identify that task as a "major milestone". I have a formula written to show the next upcoming milestone date, but need to, based on the date referenced,…
-
Finding Average of Cells in Another Sheet
Hello, I am trying to find the average of "% completes" percentages from one sheet to another that meet a certain criteria. Here is the formula I have attempted however, I am receiving an "Invalid Operation". Is there a way to accomplish this without selecting each cell individually? =IF({Team} = "Marketing", AVG({%…
-
Comments/Discussions from Forms
I would like to be able to map to the comments/conversations string from form entry. I have seen a couple of questions about this over the last year, and the answer has been, "Unfortunately, it's not possible at the moment, but it's an excellent idea!". Just wondering if this was ever achieved? As a work around, I just a…
-
RYGB Lights Not Changing with Dates
I am not sure why my lights wont change based on the dates: =IF([Original Due Date]@row < TODAY(14), "Yellow", IF([Original Due Date]@row < TODAY(7), "Blue", IF([Revised Due Date]@row > [Original Due Date]@row, "Red"))) Would like: Yellow: <14 days from today Blue: <7 days from today Red: Revised Date < Original Date
-
Formula to update a value
I would like my status field to automatically update based on the % complete. If 0% completed = "Not Started", If 100% = "Completed", and if anything over 0 but under 100 = "In Progress" This is what I have so far but it is not picking up any of this formula except for =0 and so if the % is 100 this is returning In…
-
How to count number of vendors in each country?
Hey Community, I am simply trying to calculate the quantity of contracts assigned to each vendor per country (Ex: I want to see that IBM is covering 100 contracts for us in the United Kingdom). The columns I have to use for this are the following: Vendor assigned, Covered Country (on the same sheet) Countries, Vendors (on…
-
How to get distinct values from "Host Manager Name" and "Active Contractors"?
I am trying to consolidate the "Host Manager Name" column to state the name once in the "Distinct Name" column. I would like the same for the "Active Contractors" column, to state the number once in the Max Number Column. I followed the instructions from the link below: It worked for the first row, but as I dragged the…
-
Selecting a week number based on custom criteria
Hi everyone! I'm trying to avoid a nested if statement such as: if([start]@row>"10/4/21" AND [start]@row<"10/8/21",1,if{[Start]@row>"10/10/21" AND . . . and so on for the next 25 weeks. My initial thought was to do something like look at [start]@row, reference helper sheet {start/end}, output helper sheet {week number},…
-
WBS Numbering
Hi I searched the help but could only find a 2018 listing. What I would like to have is an auto-numbering that is parent dependent so that we have e.g. 1 - Parent 1.1, 1.2, 1.3 - children If there was another parent under the master, that would become 1.1.1; 1.1.2 etc Is this possible or does it need to be a manual entry?…
-
Formula Help
Hello! 2 things... I have a sheet summary where I am using the formula below to count the true number of students on the sheet even though they can be listed multiple times per semester. It works like a charm because the email address is the column used because there is no way there could be a typo there. Sometimes editors…