-
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.
-
populate column based on parent rows
Hi All, Need some guidance is there way to automate column 20 to auto populate whatever the parent cell on task name? @Paul Newcome It's Maz Uddin, this is a new account. Please can you answer if you have time.
-
Using COUNTIFS with ranges of different size
COUNTIFS( range1, criterion1, [ range2, criterion2... ]) OK, I'm stuck. When using COUNTIFS, and both ranges only cover 1 column each, or both ranges cover multiple column, it works perfectly. But, when for example, range 1 covers only 1 column and range 2 covers more than 1 column, I get the following error message:…
