-
What formula will return one of three date fields based on set critera?
Okay, so here's another one for the community We have a tracker with three date fields. The PM Received date #1 should take priority and be returned if not blank, otherwise, we want one of the other two dates, whichever is not blank. (and only one of #2 & #3 will be not blank) PM Received Intake Date: The actual date…
-
Cross Sheet Reference Maximums
Hello! Does anyone know if cross sheet reference maximums increased since Engage? I know sheet capacity increased and cell link capacity increased, but I don't recall cross sheet references specifically being called out. As far as I am aware (and is still documented: Smartsheet Help Page), there is a maximum of 100 cross…
-
Index/Match with Has and Specific Select
I have an master roster and each week I need to ensure a response for each. Since each responder has mutliple areas of responsibility, one submission may cover multiple items from the master roster. I used an index/match formula however when there are multiple entries in a single line, I get a #No Match. And now that the…
-
Subtraction Formula?
I have a value of 02 and I would like to have a formula that would return 01. Is the a way to do this? 02-?? = 01
-
Matrix Sheet based on date and personal resource assigned
Hello all I'm a bit stumped by this and would like assistance on how to create my formulas. I'm tasked to create a sheet, view, or dashboard to summary our master data sheet turning it into a matrix style sheet. This matrix style sheet will pull data from several other databases within smartsheets. The matrix should…
-
Counting Entries Satisfying a Condition and Within a Certain Month/Year
Hello Everyone! I am trying to create a formula that counts the number of times a condition is satisfied for each month/year. My sheet has a column "Parameter" that either has the value "OFF" or "ON". I would like to create a formula that counts the number of times "OFF" occurs per month. However, the dates in my data…
-
How to count criteria in a multi-select column
I have a sheet that tracks the priority level of funding requests and I am trying to formulate a metric that 1) counts the number of requests under a particular priority level and 2) only counts the request if it is under a particular Division Group. The issue I am running into is that the priority levels are multi-select,…
-
Formula for identifying schedule conflicts with multiple assigned people
I have been trying with no success to create a formula to identify schedule conflicts if multiple people are assigned. The formula works fine with a single person assigned. For the test case, I am using a separate table (on the left) to identify when people will not be available. The right side is representative of typical…
-
Count Number of Days Past Due
Ultimate goal: Create a formula that will count the number of days an item is past due, while giving credit (by counting it as zero) for items completed early or on time all while ignoring where a due date may have an "N/A" or other issue in the field. This formula will then be used to create an average day past due…
-
Formula to populate a cell based on another cell's formula criteria
Hi Again, I have a formula in row 1 of column Project Status that identifies the last value in the column that has any status except Not Started: =INDEX(COLLECT([Project Status]:[Project Status], [Project Status]:[Project Status], <>"Not Started"), COUNTIF([Project Status]:[Project Status], <>"Not Started")) In the example…
