-
What Formula for one "Master ID" with multiple data rows and columns?
Hello, I am trying to use a formula in "7.4 Test" that pulls the same "Master ID" info from "7.3 Test", but the data can be random if it is "Red Phase", "Green Phase", or "Blue Phase" there will always be steps 1,2,3 & 4, but it can be random which color phase comes in, so only one Color will be identified, the other…
-
What is the formula in calculating the years, months, and days of service in a given date?
Hi Everyone, Can you pls help me figure out what is wrong with my formula? I wanted to get the exact, years, months, and days of service of our employees. I was able to do this in gsheet, but having a hard time here. =YEAR(TODAY()) - YEAR([Date Hired]10) - IF(DATE(YEAR([Date Hired]10), MONTH(TODAY()), DAY(TODAY())) < [Date…
-
Return a task name that corresponds to the row in the predecessor column
Hi there - I'm trying to get the text of the Task Name to show up in a column called "Pre-Reqs" that corresponds to the row number in the Predecessor column. I created a Row ID column, bc there's no easy way to reference rows. I've tried lookup and if statements and can't get it to work. Please help!
-
Calculating the difference in years, months, and days between two date ranges
Hello, I am trying to calculate the difference between two date ranges and have the result display in years, months, and days. The formula is displayed below and also pasted here: =IF(ISBLANK(DOB@row), [Event Date]@row, (INT(([Event Date]@row) - DOB@row) / 365) + " years " + IF(MONTH([Event Date]@row) - MONTH(DOB@row) < 0,…
-
Assigning a Level Based on CHILD items
I have 4 different PARENTs with many CHILD items under each of the 4 PARENTS. The PARENT column is named "Item / Topic". I want to assign Level 1 for the first PARENT, Level 2 for the second PARENT and so on. The first PARENT is RISKS. This formula worked and assigned a value of 1. Also, if there is a CHILD items that…
-
Trying to find specific headcount for each month of arrivals and how to include End Date
Hello All, I have a formula currently counting the arrivals for a headcount but it is not counting who is staying past that month. How can I change my formula to accomodate not only new arrivals but also the current personnel that are there for future months. I.E. New arrivals + Current personnel =COUNTIFS({TA Management…
-
INDEX MATCH (Multiple Criteria) vs INDEX COLLECT
Hello Smartsheet Community, I have a metric sheet set up where I am pulling from a much larger budget sheet - on my metric sheet, I want to pull in the market and due dates based on the project description. Nearly all projects have a unique description and my INDEX MATCH function works without issue; however, there are a…
-
Average Collect Formula
i want to add =Average(Collect()) to the formula below while wanting to know the average for {Formal Total}. The below is the criteria for it. ({Formal Capacity}, >=10, {Formal Capacity}, <=269, {Date Budget Formal}, IFERROR(YEAR(@cell ), 0) = 2021)
-
Count Collect formula crashing large sheet - Scheduling conflict identification
Hello, I am running a Count(Collect) on a sheet with 8000 records. The formula calculates for a small number of records but if I make it a column formula or copy it to every cell then the sheet crashes. Does anyone know how to count thousands of rows that meet certain conditions without crashing the sheet? Purpose of…
-
Need to use the formula in Contact Cell
Dear, I have a condition where i need to add a contact cell value by default based on other column value. For EX: When one row is submitted if the location enter is "ABC" then i need to choose contact cell(Project Manager) as A If Location is "DEF" then choose contact cell as B. Do we have any solution for this.
