-
Clarification regarding Average(collect)?
I'm trying to calculate the average lifespan for a specific machine in a specific area, but am running into an issue with my average(collect) formula. Here's the formula I have right now, and it only returns as unparseable. =AVERAGE(COLLECT({Lifespan},{Machine},"1",{Row},"1",{Position},"1")) In this instance, its to…
-
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…
-
Workflow for After Hours Tickets
Hi all, I currently manage our support tickets smartsheet and live on east coast. I have a team member who lives on West Coast and I want to create a workflow to have any tickets submitted between the hours of 5pm EST and 8PM EST sent to him with a notification. Currently, I am capturing the date/time ticket is submitted…
-
I think I need an index match formula as source sheet is unsorted
Hi, people smarter than me. I have been trying for days and days to get a formula to work. The source data is not sorted; it is populated by form data. The vloopup formula only duplicated the same result from the first row over again. =IF(VLOOKUP([Primary Column]@row, {2024 Circulation File Num to Support Level}, 1, false)…
-
Percentages lost when indenting linked cells aka best way for roll up smartsheet
Hello I am trying to connect about 50 project plan sheets on one sheet. The requested configuration is a traffic indicator for a customer then the ability to drill down to see each customer project and status then a drill down for high-level milestones. FYI I have just read about the limitations so I understand eventually…
-
Incorrect Argument Set error when using Vlookup to reference another sheet.
I am using a Vlookup to use a row id in sheet A to lookup the corresponding row id in sheet B and return the value in the status column - the syntax looks correct to me but I am getting an Incorrect Argument Set error. This is the syntax I am using: =VLOOKUP([Row ID]@row, {Cutover Tasks Range - Row ID}, 6, false) Can you…
-
How to calculate using a 7 day workweek (to replace NETWORKDAY)?
I am using this formula to calculate the # of days it takes to get to a particular task called, Enable. Range 2 = %Complete and Range 1 = Start Date of Enable. It calculates correctly using 5 days per week. I am now being asked to count using 7 days per week. Is there a comparable function that I can swap out for…
-
Combine Contacts from multiple columns into a single cell
Allowing contact columns to be combined into a multi-select contact column would make team management and index sheets much more useful. If Columns 2 through 10 on my sheet are all contact columns, a formula like =Contact2@row + Contact3@row + Contact6@row should produce a multicontact cell, not a text cell. That would…
-
Creating a unique ID based on two other columns
I'm trying to create a unique id based on values in 2 columns: partner and label. In our data, we have names associated with partners, but when we share data we don't want to share names, so instead I'd like to create IDs. As you can see in the sample below, we use two columns because names can be repeated between…
-
What is the longest formula you've created?
As the title suggests, I am very curious to see how far some of you all have pushed the limits of Smartsheet formulas. I think it will be interesting to see everyone's longest formula, and what they created this formula for. I'll go first: A person on the Smartsheet community was requesting a column to have employees names…