-
% Complete based on efforts without lag in duration
Hi everyone, wondering if someone has solution for this. I am looking for a formula that will be within a single cell (not to create additional rows) that will MULTIPLY only if the DURATION is a child (=IF(COUNT(CHILDREN())) with the % COMPLETE of that specific duration. At the moment I have 2 solutions: Single cell with…
-
Multiple dependencies
Hi, I have an issue that I'm not able to solve: In this example I tried to define the beginning and the end of task 3 to be dependent on task 1 and task 2, respectively. I want Task 3 to start when task 1 starts, and for the end of the event “finish-finish” with task 2. As you can see, it automatically sets the correct end…
-
How to get a lookup (Vlookup or Index Match) to return multiple values?
I'm essentially trying to have one cell populate with text values from multiple different cells based on a certain criteria. What I really wish I had is a JOINIF function, if that explains my problem better. Thanks in advance!
-
Date Calc Not Working
I feel like I am going crazy here.... I have a formula in the first column (formatted as a number) to take 04/27/22 and subtract 04/17/22 (both columns are formatted as date columns) but the result is 14? When I take the 04/17/22 and add that same cell of "14" I get 05/01/22. Has anyone else run into similar issues when…
-
Cannot get date to show correctly after upload of spreadsheet (xlsx)
I upload most of my data via data shuttle as they are imports from other systems used in our corp. I have an excel sheet which i upload and has dates in the format of date/time and I only want the date to use in metrics sheets I have created. I have tried using Left and DateOnly functions in the next cell to convert to a…
-
Help with Index/Match
Hello! I am trying to put together a formula that I could use some help on. I have 2 different sheets, Boarding Passes and Open Cases. In Open Cases, I have a Case Number. In Boarding Passes, I have a Case Number(s) field that could reference more than one of the case numbers in Open Cases (these should be separated by a…
-
INDEX COLLECT or VLOOKUP not working with numbers
I am in need of some help with trying to use either the INDEX/COLLECT or VLOOKUP functions. I have tried everything I can think of and nothing works. By employee number, I want to return their tenure. Both are contained in a reference sheet. Here is what I have tried. =IFERROR(INDEX(COLLECT({Tenure }, {Employee Number},…
-
COUNTIFS CONTAINS problems
Hello, I am trying to create a formula that will count the cells across spreadsheets using COUNTIFS and CONTAINS. My formula works, however I don't believe it is accurate since I am getting a count of 0 for every scenario I try and apply. I would like to count the "Requested Delivery Quarter", shown in screenshot two. By…
-
Using SUMIFS together with AVG returning #UNPARSEABLE
Hello, I am getting the #UNPARSEABLE error in my formula to sum totals between a date range and return the average of the total sum. The formula seems to work without the AVG syntax. Working formula: =SUMIFS({POTotal}, {OrderDate}, >=DATE(2020, 5, 1), {OrderDate}, <=DATE(2020, 12, 31)) #UNPARSEABLE formula:…
-
Is there a formula to determine what the next task is?
I would like the Smartsheet Summary to specify what Task needs to be completed next. To make the determination, I think I need to search the the Status column for those items "Not Started" or "In Progress" and then the Due Date column of those items for the one with the earliest due date. The Task (Task column) that meets…