-
Join, Collect functions
Hi All I am trying to retrieve results from several columns on a seperate sheet into 1 cell on another sheet. This formula works - =IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM Name}, 0)) = "Not achieved", "SUS Core", " ") But as soon as I add more columns - =JOIN(
IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM…
-
Wildcard workaround
Hi, Trying to get some counts for a report and have run into a bit of a road block. In excel, I would typical use a count formula and a wildcard. However, Smartsheet doesnt seem to have the capability. Below if my code: =COUNTIFS({Papers Range 2}, "*O'Malley*", {Papers Range 3}, "1 - Published") How would I edit this to…
-
Calculating a running tally of time
Greetings colleagues - I'm stuck and could use some help. I have a checklist that needs to calculate the Start and End time (hh:mm) given the first start time and duration in minutes for each step in my checklist. All of my columns are Text/Num. I read countless posts and got the Row@row-1 set up so a helper column will…
-
HELP! Column Formula working on some rows - not others
Hi! I'm stumped on this one. I am using a column formula to apply a date 21 days prior to the date in another column. Everything (formatting, etc) is the same on all the rows, but I keep getting "Invalid Value" errors on some rows and in other rows it works great. Does anyone have any ideas?? Formula: =DATE(YEAR([GT…
-
How to use LARGE function using criteria. AKA "How can I get the #1 risk based on criteria
I included all this information in the snippet but here is my issue. The issue is I cannot get a formula where the "Risk/Issue" column identifies whether it is a RISK or an ISSUE or an RISK BECOME ISSUE, and then give me the #1 item depending on that criteria, then the #2 item depending on that criteria, etc. My current…
-
How to capture the value (end result) of a formula for use in other processes
I've got formulas that return a result of a specific number depending on different keywords in certain cells on the spreadsheet. That's fine - they all work great! My problem is that I'd like to use those numbers (the end result) in other spaces in order to calculate totals for certain programs, by month and by year. But…
-
calculate maximum date
Hi everyone, I need your help to calculate the maximun data for each resource, starting from a sheet where the activities are recorded. I have a sheet where I write all tasks for each resource: I need to have the maximum date present in the "task" sheet for each resource present in the "resources" sheet Can you please help…
-
What formula can I create to get the correct SLA?
I know I can create a column to only include the date from the ticket was submitted and another for calculating the business days. However, I'd like to auto-populate the deadlines based on the triage options. Based on the selection of Triage options auto-populate a deadline from the time the ticket was submitted: Tier 1 –…
-
Multiple formulas on one sheet
Hello, I currently have a formula in column 1 of my sub-sheet copying data from my main source sheet. I'm trying to create a similar formula in column 2 of the same sub-sheet referencing my main source sheet. When I do this, the formula in column 2 is changing the formula in column 1. What am I doing wrong? The formula for…
-
How to average the time between various dates if conditions are met
I'm building a spreadsheet with monthly metrics and having issues creating a formula. There will be a separate smartsheet with individual events (1 row per event). Each event has ~3 review cycles. Each review cycle includes the date the draft was provided & the date the draft returned. Formula I'm trying to build: If the…
